SQL: Naming PRIMARY KEY, CHECK and UNIQUE Constraints

For many years, SQL Server related tooling has been pushing us to use constraints without naming them ourselves. But SQL Server requires these to have names so it generates names for them. These system-generated names aren’t very helpful.

What’s the issue with system-generated names?

The first issue with them is that they can cause havoc with deployment and schema-comparison tooling unless you find and use options to ignore any system-generated names.

The second issue is that when they are violated, they are returned to the client, and they are generally pretty meaningless.

What to do instead

So, I’m not a fan of letting the system automatically name constraints, and that always leads me to thinking about what names I really want to use.

And note that any time you discuss naming, you’ll have someone who disagrees because they have their own way to do so. I’m ok with that. This is how I think it should be done. I’m not necessarily saying you’re wrong if you do something else, particularly if you are at least consistent.

Primary key constraints

Naming Primary keys is pretty easy. I’m not a fan of prefixes in general, but constraints is one area where I think differently. There is a pretty much unwritten rule that SQL Server people mostly name them after the table name. For example, if we use a system-generated name like this:

USE tempdb;
GO

CREATE TABLE dbo.Clients
(
    ClientID int IDENTITY(1, 1) PRIMARY KEY

A violation of the constraint will return a message like:

The name isn’t helpful and it shows us the key value but not which column was involved.

So, instead, we might say:

CREATE TABLE dbo.Clients
(
    ClientID int IDENTITY(1, 1) 
        CONSTRAINT PK_Clients PRIMARY KEY

Even in this case, there are a few questions. First question is whether the name includes the schema (ie: PK_dbo_Clients) If not, this design has an issue if there are two or more tables in different schemas with the same table name. This then leads us to:

CREATE TABLE dbo.Clients
(
    ClientID int IDENTITY(1, 1) 
        CONSTRAINT PK_dbo_Clients PRIMARY KEY

Second question is if the name should include the columns that make up the key? (ie: PK_dbo_Clients_ClientID) This might be useful when an error message is returned. A message that says that you violated the primary key, doesn’t tell you which column (or columns) were involved.

That would lead to:

CREATE TABLE dbo.Clients
(
    ClientID int IDENTITY(1, 1) 
        CONSTRAINT PK_dbo_Clients_ClientID PRIMARY KEY

My concern with including the column or columns in the naming, is that it can get pretty messy, particularly where the keys involve multiple columns.

My current preference is to just use PK_SchemaName_TableName.

CHECK and UNIQUE constraints

CHECK constraints (and UNIQUE constraints) are more interesting. Consider the following constraint definition and failing insert:

CREATE TABLE dbo.Clients
(
    ClientID int IDENTITY(1, 1) 
        CONSTRAINT PK_dbo_Clients_ClientID PRIMARY KEY,
    ClientName nvarchar(100) NOT NULL,
    RetailKey int NULL,
    OnlineKey int NULL,
    CHECK (RetailKey IS NOT NULL OR OnlineKey IS NOT NULL)
);

INSERT dbo.Clients (ClientName, RetailKey, OnlineKey)
VALUES (N'Fred', NULL, NULL);

When this violation occured, the error returned was:

Note how (relatively) useless this is for the user. We could have named the constraint like so:

CREATE TABLE dbo.Clients
(
    ClientID int IDENTITY(1, 1) 
        CONSTRAINT PK_dbo_Clients_ClientID PRIMARY KEY,
    ClientName nvarchar(100) NOT NULL,
    RetailKey int NULL,
    OnlineKey int NULL,
    CONSTRAINT CK_dbo_Clients_Either_RetailKey_or_OnlineKey_must_be_provided
        CHECK (RetailKey IS NOT NULL OR OnlineKey IS NOT NULL)
);

Note how much more useful the error becomes:

And if we don’t mind spaces in names, we might remove the underscores and delimit the name to make it more readable:

    CONSTRAINT [CK_dbo_Clients Either RetailKey or OnlineKey must be provided]
        CHECK (RetailKey IS NOT NULL OR OnlineKey IS NOT NULL)
);

I know that’s not for everyone, but that would return:

Having embedded spaces in the names raises another whole raft of questions. I never mind that in objects that are meant to be read by humans. But that’s a topic for another day.

My current preference is the version with the underscores. It’s still 100% readable by humans. And I do similar naming for UNIQUE constraints, except with a UQ prefix.

I’d like to hear your thoughts on this. How do you name your constraints?

2026-06-13