Skip to content
Related Articles

Related Articles

Improve Article

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).

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

  • 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_NAMECONSTRAINT_TYPE
    XYZSTATUS<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:

    OWNERCONSTRAINT_NAMETABLE_NAMECOLUMN_NAMEPOSITION
    SYSTEMXYZSDFSTATUS(null)
    SYSTEMABCSDFSTATUS1
  • 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_NAMECONSTRAINT_TYPECOLUMN_NAMETABLE_NAME
    XYZCSTATUSSDF
    ABCPSUPPNOSDF
My Personal Notes arrow_drop_up
Recommended Articles
Page :