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 |
Share your thoughts in the comments
Please Login to comment...