SQL | Checking Existing Constraints on a Table using Data Dictionaries

Prerequisite: SQL-Constraints

In SQL Server the data dictionary is a set of database tables used to store information about a database’s definition. One can use these data dictionaries to check the constraints on an already existing table and to change them(if possible).

• USER_CONSTRAINTS Data Dictionary: This data dictionary contains information about each constraint used in a database along with constraint specific information.

  DESC USER_CONSTRAINTS;
  
  Name              Null     Type         
  ----------------- -------- ------------ 
  OWNER                      VARCHAR2(30) 
  CONSTRAINT_NAME   NOT NULL VARCHAR2(30) 
  CONSTRAINT_TYPE            VARCHAR2(1)  
  TABLE_NAME        NOT NULL VARCHAR2(30) 
  SEARCH_CONDITION           LONG         
  R_OWNER                    VARCHAR2(30) 
  R_CONSTRAINT_NAME          VARCHAR2(30) 
  DELETE_RULE                VARCHAR2(9)  
  STATUS                     VARCHAR2(8)  
  DEFERRABLE                 VARCHAR2(14) 
  DEFERRED                   VARCHAR2(9)  
  VALIDATED                  VARCHAR2(13) 
  GENERATED                  VARCHAR2(14) 
  BAD                        VARCHAR2(3)  
  RELY                       VARCHAR2(4)  
  LAST_CHANGE                DATE         
  INDEX_OWNER                VARCHAR2(30) 
  INDEX_NAME                 VARCHAR2(30) 
  INVALID                    VARCHAR2(7)  
  VIEW_RELATED               VARCHAR2(14) 
  

  Constraint types are:

  C - Check constraint on a table  
  P - Primary key  
  U - Unique key  
  R - Referential integrity  
  V - With check option, on a view  
  O - With read only, on a view  
  H - Hash expression  
  F - Constraint that involves a REF column  
  S - Supplemental logging
  

  Now consider the following source table “SDF”:

  
  
  

  SUPPNO  SNAME   STATUS  CITY
  21    JONYY    25    NY
  22    MIKKY    11    LA
  23    JIM    29    LV
  24    BNFERYY    47    HW
  25    TIM    41    HS
  

  Query for checking constraints on this table :

  SELECT CONSTRAINT_NAME, SEARCH_CONDITION AS CONSTRAINT_TYPE 
  FROM USER_CONSTRAINTS 
  WHERE TABLE_NAME='SDF';
  

  Output:

  CONSTRAINT_NAME	CONSTRAINT_TYPE
  XYZ	STATUS<50
  Abc	(NULL)

• USER_CONS_COLUMNS Data Dictionary: We can use this Data Dictionary to find the columns to which constraint has been applied.

  DESC USER_CONS_COLUMNS;
  
  Name            Null     Type           
  --------------- -------- -------------- 
  OWNER           NOT NULL VARCHAR2(30)   
  CONSTRAINT_NAME NOT NULL VARCHAR2(30)   
  TABLE_NAME      NOT NULL VARCHAR2(30)   
  COLUMN_NAME              VARCHAR2(4000) 
  POSITION                 NUMBER     
  

  Query to check the columns of SDF table with constraints:

  SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME='SDF';
  

  Output:

  OWNER	CONSTRAINT_NAME	TABLE_NAME	COLUMN_NAME	POSITION
  SYSTEM	XYZ	SDF	STATUS	(null)
  SYSTEM	ABC	SDF	STATUS	1

• Using Cartesian Product to get Complete information on constraints:

  SELECT A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE, B.COLUMN_NAME, B.TABLE_NAME
  FROM 
  USER_CONSTRAINTS A,
  USER_CONS_COLUMNS B
  WHERE A.CONSTRAINT_NAME=B.CONSTRAINT_NAME
  AND A.TABLE_NAME='SDF';
  

  Output:

  CONSTRAINT_NAME	CONSTRAINT_TYPE	COLUMN_NAME	TABLE_NAME
  XYZ	C	STATUS	SDF
  ABC	P	SUPPNO	SDF