This is a short and sweet article discussing the importance of naming database constraints. It's written from the perspective of someone mostly familiar with SQL Server databases.

What is a constraint?

Put briefly, a constraint is a database rule of some form. The various types of constraints supported by SQL Server are:

• Not Null
• Unique
• Check
• Primary Key
• Foreign Key
• Default
• Index

When these constraints are added, they define some behavior internally in the database - typically enforcing rules such as referential integrity, uniqueness, or content validation. Constraints can also have impacts on indexes in the case of key constraints and index constraints.

Constraint Names

Constraints must have a name for database reference purposes. However, the basic SQL Syntax does not require constraint names, so in cases where a constraint is added without a name, SQL Server will automatically generate a name for you.

For example:

CREATE TABLE Resumes
(
  ID INT NOT NULL,
  Name NVARCHAR(50) NULL,
  UNIQUE (ID)
)

In this case, the UNIQUE constraint will be added with a randomized name that is something like the following: UQ__Constrai__1234AB67C890D123

• Note that in this example, you would more likely use a primary key, but for purposes of illustration we're using a unique constraint *

Naming a Constraint

The problem with a constraint that has a randomized name is that it becomes difficult to drop the constraint as part of a change script down the line if the production and quality assurance databases have different constraint names to represent the same type of a constraint.

Because of that, it's important to name a constraint whenever you create it. Check the syntax for each type of constraint you're working with, but our earlier example would become:

CREATE TABLE Resumes
(
  ID INT NOT NULL,
  Name NVARCHAR(50) NULL,
  CONSTRAINT UC_ResumeID UNIQUE (ID)
)

From there, you could then drop UC_ResumeID by name easily, regardless of which environment the script was run on.

Once again, this is a bit of a straw man example as you'd typically use a primary key, but this should illustrate the core problems that can arise without explicitly named constraints.