What Is the Default Constraint Name in SQL Server?

Problem:

You want to know the default names of constraints in the SQL Server database.

Example:

In our database, we create two tables, country and student, with the following constraints: PRIMARY KEY (the column id in the tables country and student), FOREIGN KEY (the column country_id in the table student), DEFAULT (the column name in the table student), UNIQUE (the column name in the table country and the column personal_number in the table student), and CHECK (the column age in the table student).

Solution:

CREATE TABLE country (
id int NOT NULL primary key,
name varchar(100) UNIQUE
);

CREATE TABLE student (
id int NOT NULL primary key,
name varchar(100) NOT NULL DEFAULT 'unknown',
personal_number varchar(100) UNIQUE,
age int CHECK(age>15),
country_id int FOREIGN KEY references country(id)
);

Let’s display how SQL Server names the constraints of these tables by default. Use this query to see them:

SELECT TABLE_CATALOG, TABLE_NAME,
       CONSTRAINT_TYPE,CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

Here is the result:

Discussion:

When we create our tables, we set constraints for some columns. However, we don’t specify the names for these constraints, so they are named by default. How are the default names for constraints generated?

In SQL Server, the default constraint names start with specific prefixes: PK, UQ, CK, or FK.

The default name for a PRIMARY KEY constraint starts with ‘PK’, followed by underscores (‘__’), the table name, more underscores (‘__’), and a hexadecimal sequence number generated by SQL Server. In our example, PK__country__3213E83F7EFD826D is the default name for the primary key constraint in the table country and PK__student__3213E83F357C7D1D is the default name for the primary key constraint in the table student.

The default name for a FOREIGN KEY constraint starts with ‘FK’, followed by underscores (‘__’), the table name, more underscores (‘__’), the name of the related table, and a hexadecimal sequence number generated by SQL Server. In our example, FK__student__country__4F7CD00D is the default name for the foreign key constraint in the table student which is equivalent to the column id in the table country.

The default name for a UNIQUE constraint is ‘UQ’, underscores (‘__’), the table name, more underscores (‘__’), and a hexadecimal sequence number generated by SQL Server. In our example, UQ__country__72E12F1BB69A1D8C is the default name of the unique constraint for the column name in the table country, and UQ__student__24E2EDD2AC2A7D87 of the UNIQUE constraint for the column name of the table student. UNIQUE constraints with multiple columns are named in a similar pattern.

The default name for a CHECK constraint is ‘CK’, underscores (‘__’), the table name, more underscores (‘__’), the column name, more underscores (‘__’), and a hexadecimal sequence number generated by SQL Server. In our example, CK__student__age__4E88ABD4 is the default name for the CHECK constraint for the column age of the table student.