Back to cookbooks list Articles Cookbook

How to Find the Name of a Constraint in SQL Server

Problem:

You want to find the names of the constraints in a table in SQL Server.

Example:

We want to display the names of the constraints in the table student.

Solution:

SELECT TABLE_NAME,
       CONSTRAINT_TYPE,CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME=’student’;

Here is the result:

TABLE_NAMECONSTRAINT_TYPECONSTRAINT_NAME
studentPRIMARY KEYPK__student__3213E83F357C7D1D
studentUNIQUEUQ__student__24E2EDD2AC2A7D87
studentCHECKCK__student__age__4E88ABD4
studentFOREIGN KEYFK__student__country__4F7CD00D

Discussion:

Use the view table_constraints in the information_schema schema. The column table_name gives you the name of the table in which the constraint is defined, and the column constraint_name contains the name of the constraint. The column constraint_type indicates the type of constraint: PRIMARY KEY for the primary key type, FOREIGN KEY for the foreign key type, UNIQUE for the unique values, and CHECK for the constraint check.

For example, the primary key in the student table has the constraint name PK_student_3213E83F357C7D1D. The constraint_type column gives you information about the type of each constraint; for the primary key, it is PRIMARY KEY. The table_name column in this view indicates which table contains this constraint.

However, if you would like to select the DEFAULT constraint from a database, use this query below.

SELECT c.name, c.definition
FROM sys.default_constraints c
JOIN sys.objects o ON o.object_id = c.parent_object_id
WHERE o.name ='student';

Here is the result:

namedefinition
DF__student__name__4D94879B("unknown")

The name of the DEFAULT constraint is stored in the column name, but the value is in the column definition. Joining the views sys.default_constraints and sys.objects allows us to select only the data for a given table (in our example, the table student) with using WHERE clause. The constraint name contains the name of the table (student) and the name of the column(name).

Recommended courses:

Recommended articles:

See also: