When you create a CHECK constraint in SQL Server, you might not even think about whether it’s a table-level constraint or a column-level constraint.
A table-level CHECK constraint applies to the table, whereas a column-level constraint applies to a specific column. With a table-level CHECK constraint, it’s the row that is checked when it checks the data. With a column-level CHECK constraint, it’s the specific column that is checked.
Generally you’ll know whether or not the constraint you’re creating is a table-level or column-level constraint by the definition you give it. If only one column is being checked in the expression, it will be a column-level constraint. Otherwise it will be a table-level constraint.
But how do you know if your existing constraints are column-level or table-level?
You can run any of the code examples below to determine whether your existing constraints are column-level or table-level. These retrieve all CHECK constraints for the current database, but you can always use a WHERE clause to narrow it down to a specific constraint.
Example 1 – Basic Query
Here’s a simple query that returns basic information about all CHECK constraints in the current database.
Here, I query the sys.check_constraints system view (which returns a row for each object that is a CHECK constraint, with sys.objects.type = 'C'). I only return four columns (but feel free to return as many columns as you like).
SELECT Name, OBJECT_NAME(parent_object_id) AS 'Table', parent_column_id, Definition FROM sys.check_constraints;
Result:
+-----------------+----------------+--------------------+----------------------------------------+ | Name | Table | parent_column_id | Definition | |-----------------+----------------+--------------------+----------------------------------------| | chkPrice | ConstraintTest | 2 | ([Price]>(0)) | | chkValidEndDate | ConstraintTest | 0 | ([EndDate]>=[StartDate]) | | chkTeamSize | ConstraintTest | 3 | ([TeamSize]>=(5) AND [TeamSize]<=(20)) | | chkJobTitle | Occupation | 3 | ([JobTitle]<>'Digital Nomad') | +-----------------+----------------+--------------------+----------------------------------------+
The quickest way to determine which constraints are table-level constraints is to look for the zero (0) in the parent_column_id column. Anything with a zero is a table-level CHECK constraint. A non-zero value indicates that it’s a column-level CHECK constraint defined on the column with the specified ID value.
So in this example there are three column-level constraints and one table-level constraint.
Note that there are two constraints with the same parent_column_id (3), however, these two constraints are from different tables. The 3 refers to the third column of their respective tables.
As mentioned, if you only want info on a specific constraint, use a WHERE clause:
SELECT Name, OBJECT_NAME(parent_object_id) AS 'Table', parent_column_id, Definition FROM sys.check_constraints WHERE name = 'chkPrice';
Result:
+----------+----------------+--------------------+---------------+ | Name | Table | parent_column_id | Definition | |----------+----------------+--------------------+---------------| | chkPrice | ConstraintTest | 2 | ([Price]>(0)) | +----------+----------------+--------------------+---------------+
Example 2 – Improve the Query
We can improve on the previous example by returning the parent column name instead of just its ID. Of course, this will return the column name for column-level constraints only. For table-level constraints NULL will be returned.
SELECT cc.name AS 'Constraint', o.name AS 'Table', ac.name AS 'Column', cc.Definition AS 'Constraint Definition' FROM sys.check_constraints cc LEFT OUTER JOIN sys.objects o ON cc.parent_object_id = o.object_id LEFT OUTER JOIN sys.all_columns ac ON cc.parent_column_id = ac.column_id AND cc.parent_object_id = ac.object_id;
Result:
+-----------------+----------------+----------+----------------------------------------+ | Constraint | Table | Column | Constraint Definition | |-----------------+----------------+----------+----------------------------------------| | chkPrice | ConstraintTest | Price | ([Price]>(0)) | | chkValidEndDate | ConstraintTest | NULL | ([EndDate]>=[StartDate]) | | chkTeamSize | ConstraintTest | TeamSize | ([TeamSize]>=(5) AND [TeamSize]<=(20)) | | chkJobTitle | Occupation | JobTitle | ([JobTitle]<>'Digital Nomad') | +-----------------+----------------+----------+----------------------------------------+
Example 3 – Further Improvements
Let’s tweak the query some more:
SELECT
cc.name AS 'Constraint',
cc.is_disabled AS 'Disabled?',
CASE WHEN cc.parent_column_id = 0
THEN 'Table-level'
ELSE 'Column-level'
END AS 'Table/Column',
o.name AS 'Table',
ISNULL(ac.name, '(n/a)') AS 'Column',
cc.Definition AS 'Constraint Definition'
FROM sys.check_constraints cc
LEFT OUTER JOIN sys.objects o
ON cc.parent_object_id = o.object_id
LEFT OUTER JOIN sys.all_columns ac
ON cc.parent_column_id = ac.column_id
AND cc.parent_object_id = ac.object_id;
Result:
+-----------------+-------------+----------------+----------------+----------+----------------------------------------+ | Constraint | Disabled? | Table/Column | Table | Column | Constraint Definition | |-----------------+-------------+----------------+----------------+----------+----------------------------------------| | chkPrice | 0 | Column-level | ConstraintTest | Price | ([Price]>(0)) | | chkValidEndDate | 0 | Table-level | ConstraintTest | (n/a) | ([EndDate]>=[StartDate]) | | chkTeamSize | 0 | Column-level | ConstraintTest | TeamSize | ([TeamSize]>=(5) AND [TeamSize]<=(20)) | | chkJobTitle | 0 | Column-level | Occupation | JobTitle | ([JobTitle]<>'Digital Nomad') | +-----------------+-------------+----------------+----------------+----------+----------------------------------------+
So I now have the text “Column-level” or “Table-level” being returned, depending on which one it is.
I also use the ISNULL() function to turn any NULL values into “(n/a)”.
And I’ve also added the is_disabled column to the list, just in case any of the constraints have been disabled. You could always give this column the same treatment as the parent_column_id column and present “Yes” or “No” or “Enabled” or “Disabled” or similar.