SQL Interview: 5: System defined primary key names

SQL Interview: 5: System defined primary key names

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Intro

Question:

If you create a table using the following code, what would the name of the primary key constraint look like?

CREATE TABLE Sales.CustomerGroups
(
    CustomerGroupID int IDENTITY(1, 1) PRIMARY KEY,
    CustomerGroupName nvarchar(50) NOT NULL
);

How could you specify the name?

Can you suggest any advantages of providing a name?

Answer:

If you don’t specify the name of a primary key, the system will allocate a name similar to this:

PK__Customer__9AA3001A15FDE023

It’s basically PK some underscores, part of the table name, some more underscores, and part of a GUID string, chosen to be unique.

To specify the name, put CONSTRAINT and the name before PRIMARY KEY like this:

CREATE TABLE Sales.CustomerGroups
(
    CustomerGroupID int IDENTITY(1, 1) 
        CONSTRAINT PK_Sales_CustomerGroups PRIMARY KEY,
    CustomerGroupName nvarchar(50) NOT NULL
);

Some advantages of providing specific names are:

  • A specific name is more meaningful. It’s not obvious from the system-generated name above, even which table it’s associated with.
  • If you ever need to modify the constraint, you’ll need to know its name. It makes it much easier to write scripts when you know the name, instead of having to write code to look it up each time.
  • If the table is recreated, it will usually end up with a different name for the constraint. If you use database comparison tools, the name might be flagged as a difference between databases created with the same script. (Note that some database comparison tools have options to ignore system-generated names).

2021-04-01