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
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.