In certain designers, such as SQL Server Management Studio table designer, when you create certain constraints (Primary and foreign keys, defaults, and check constraints) they will be given a name that contains a GUID to verify the name is unique.

This can cause problems down the road when enhancing existing code for new features when you are deploying multiple software versions across multiple customer sites and need to upgrade them to new versions.

Here are some examples of names generated by the SQL engine

CONSTRAINT [STATUS_AEBEB9F0_D55F_4159_BA56_BF12F51AA541] DEFAULT (0)

CONSTRAINT [ID_37B45731_C964_4C93_8DCE_62717F5F5C4D] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]

Designers are not the only way this type of naming can occur.

While database projects help a lot by forcing you to name certain items such as indexes and foreign keys, shortcuts with defaults and primary keys can still generate a random name. For example…

CREATE TABLE dbo.TEST ( ID int NOT NULL, CONSTRAINT PK_TEST PRIMARY KEY (ID) )

You may not realize you just randomly generated a name for that primary key.

It is much better for robust code to explicitly name the constraint as follows.

CREATE TABLE dbo.TEST ( ID int NOT NULL, CONSTRAINT PK_TEST PRIMARY KEY (ID) )

I have experienced the pain first hand where a constraints were not named and in order to build an upgrade deployment for multiple upgrade paths I had to:

  1. Realize it would be a problem
  2. Loop over every constraint on the table and drop them because they were named differently in different versions
  3. Re-add the constraints with the current logic

Consider that in version 2 a constraint  was added with 1 name ID_37B45731_C964_4C93_8DCE_62717F5F5C4D.

In Version 2.5 the constraint was changed and given a new name ID_AEBEB9F0_D55F_4159_BA56_BF12F51AA541.

Now you are releasing version 3 and a feature has changed the constraint again and you need to allow direct upgrades from both version 2 and version 2.5.

It only takes a few seconds to explicitly name our constraint objects, and it can save us time in code reviews as well as deployments by using your agreed upon Naming Conventions and Standards and explicitly setting the name.

Have you ran into issues with unnamed constraints?

Let me know your experiences in the comments.