Post
Ask Question

Querying SQL Constraints

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.
  1. SELECT    
  2.     DCS.name AS CONSTRAINT_NAME,   
  3.     OBJ.name AS TABLE_NAME,   
  4.     COLS.name AS COLUMN_NAME,    
  5.     OBJECT_DEFINITION(COLS.default_object_id) AS DEFAULT_DEFINTION   
  6. FROM sys.default_constraints DCS    
  7.     JOIN sys.objects OBJ    
  8.         ON OBJ.object_id = DCS.parent_object_id   
  9.     JOIN sys.columns COLS    
  10.         ON OBJ.object_id = COLS.object_id    
  11.            AND    
  12.            COLS.column_id = DCS.parent_column_id   
  13. WHERE    
  14.     OBJ.name = 'Employee'    
  15.     AND    
  16.     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.
  1. SELECT    
  2.     CC.name AS CONSTRAINT_NAME,   
  3.     OBJ.name AS TABLE_NAME,   
  4.     COLS.name AS COLUMN_NAME,    
  5.     CC.definition AS CONSTRAINT_DEFINTION   
  6. FROM sys.check_constraints CC   
  7.     JOIN sys.objects OBJ    
  8.         ON OBJ.object_id = CC.parent_object_id   
  9.     JOIN sys.columns COLS    
  10.         ON OBJ.object_id = COLS.object_id    
  11.            AND    
  12.            COLS.column_id = CC.parent_column_id   
  13. WHERE    
  14.     OBJ.name = 'Employee'  
Output
 
 
 
Below is the query for checking the KEY constraints for both the tables.
  1. SELECT    
  2.     KC.parent_object_id,   
  3.     KC.name AS CONSTRAINT_NAME,   
  4.     OBJ.name AS TABLE_NAME,   
  5.     COLS.name AS COLUMN_NAME,    
  6.     KC.type_desc AS KEY_TYPE   
  7. FROM sys.key_constraints KC   
  8.     JOIN sys.objects OBJ    
  9.         ON OBJ.object_id = KC.parent_object_id   
  10.     JOIN sys.columns COLS    
  11.         ON OBJ.object_id = COLS.object_id 
Output
 

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