Querying SQL Constraints

Querying constraints using system query.

One of the important data while dealing with Meta data is constraints.

 

Obvious way (for most of the programmers) to look at Constraints (whether it is Check Constraint or Key Constraint or Default Constraints) is through designer and visualizing it.

 

But below is another way to take a look at the same using SQL queries. Consider the below database with two tables where there is a Foreign key relationship between department and employee tables.

 
 

Looking at the designer one would easily say that this table has primary key, a foreign key and some constraints and so on. But what if, there is a need to determine these through the query (in other words, while run time?).

 

As you can see that there is constraints defined for the column, external_id, executing below query will give you the name of the default constraints (and details if queried) for that column.

SELECT
    DCS.name AS CONSTRAINT_NAME,
    OBJ.name AS TABLE_NAME,
    COLS.name AS COLUMN_NAME,
    OBJECT_DEFINITION(COLS.default_object_id) AS DEFAULT_DEFINTION
FROM sys.default_constraints DCS
    JOIN sys.objects OBJ
        ON OBJ.object_id = DCS.parent_object_id
    JOIN sys.columns COLS
        ON OBJ.object_id = COLS.object_id
           AND
           COLS.column_id = DCS.parent_column_id
WHERE
    OBJ.name = 'Employee'
    AND
    COLS.name ='external_id'

Explanation

 

Joining the sys.default_constraints table with sys.objects and sys.columns table will give us all necessary sets require to pull the information required. As constraint is defined on the column, it is linked to that column with parent column id (where parent column id is actually a column id pointing back to sys.columns table) and we have used the same column in above query to get the specific column data.

 

The output of the above query looks like this:

 

Note: when you want to explore the definition of default values for specific column, use OBJECT_DEFINTION function that takes the object id as input parameter and returns the varchar string containing the default value of that column.

 

Similarly, if you want to query the Check constraints, you can use the below query.

SELECT
    CC.name AS CONSTRAINT_NAME,
    OBJ.name AS TABLE_NAME,
    COLS.name AS COLUMN_NAME,
    CC.definition AS CONSTRAINT_DEFINTION
FROM sys.check_constraints CC
    JOIN sys.objects OBJ
        ON OBJ.object_id = CC.parent_object_id
    JOIN sys.columns COLS
        ON OBJ.object_id = COLS.object_id
           AND
           COLS.column_id = CC.parent_column_id
WHERE
    OBJ.name = 'Employee'

Output

 

 

 

Below is the query for checking the KEY constraints for both the tables.

SELECT
    KC.parent_object_id,
    KC.name AS CONSTRAINT_NAME,
    OBJ.name AS TABLE_NAME,
    COLS.name AS COLUMN_NAME,
    KC.type_desc AS KEY_TYPE
FROM sys.key_constraints KC
    JOIN sys.objects OBJ
        ON OBJ.object_id = KC.parent_object_id
    JOIN sys.columns COLS
        ON OBJ.object_id = COLS.object_id

Output
 

 
Please leave your questions or doubts in comments, I would be happy to help.