SQL | Checking Existing Constraints on a Table using Data Dictionaries

Last Updated : 05 Sep, 2018

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

SQL | Checking Existing Constraints on a Table using Data Dictionaries

ishaan bhatnagar

Improve

Article Tags :

SQL | Checking Existing Constraints on a Table using Data Dictionaries

Similar Reads

Thank You!

What kind of Experience do you want to share?