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:

Discussion

To find the constraint name in SQL Server, 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 constraint, and CHECK for the CHECK constraint.

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:

The name of the DEFAULT constraint is stored in the column name of the view sys.default_constraints, but the value is in the column definition of the view sys.objects. 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).