Name your Constraints

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.

Comments

[Arturs Timofejevs]

I had this issue, when I first started developing my first project database I did not think to name my constraints and as such a year into the project I got access to red gates SQL Compare. Oh boy it took a couple of days to sort the small tedious differences that were not only randomly generated constraints but bad data base building practices in general that I was blind to in the beginning. Anyways I did find a nice script which will rename all constraints to use the correct convention or the one you choose.

I cannot remember where I found it but I think it was SQL Server Central.

ALTER PROCEDURE [dbo].[ImplementNamingStandard]
    @SELECT_Only        BIT = 1,
    @PrimaryKeys        BIT = 1,
    @ForeignKeys        BIT = 1,
    @Indexes            BIT = 1,
    @UniqueConstraints  BIT = 1,
    @DefaultConstraints BIT = 1,
    @CheckConstraints   BIT = 1
AS
BEGIN
    SET NOCOUNT ON;


    DECLARE @sql NVARCHAR(MAX), @cr CHAR(2);
    SELECT @sql = N'', @cr = CHAR(13) + CHAR(10);


    DECLARE @TableLimit TINYINT, @ColumnLimit TINYINT;
    SELECT @TableLimit = 35, @ColumnLimit = 35;


    IF @PrimaryKeys = 1
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- Primary Keys ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
            + REPLACE(name, '''', '''''') + ''', @newname = N''PK_' 
            + LEFT(REPLACE(OBJECT_NAME(parent_object_id), '''', ''), @TableLimit) + ''';'
        FROM sys.key_constraints
        WHERE type = 'PK'
        AND is_ms_shipped = 0;
    END


    IF @ForeignKeys = 1
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- Foreign Keys ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
            + REPLACE(name, '''', '''''') + ''', @newname = N''FK_' 
            + LEFT(REPLACE(OBJECT_NAME(parent_object_id), '''', ''), @TableLimit)
            + '_' + LEFT(REPLACE(OBJECT_NAME(referenced_object_id), '''', ''), @TableLimit) + ''';'
        FROM sys.foreign_keys
        WHERE is_ms_shipped = 0;
    END


    IF (@UniqueConstraints = 1 OR @Indexes = 1)
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- Indexes / Unique Constraints ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
   + CASE is_unique_constraint WHEN 0 THEN
   QUOTENAME(REPLACE(OBJECT_NAME(i.[object_id]), '''', '''''')) + '.' ELSE '' END
            + QUOTENAME(REPLACE(i.name, '''', '''''')) + ''', @newname = N'''
            + CASE is_unique_constraint WHEN 1 THEN 'UQ_' ELSE 'IX_'
              + CASE is_unique WHEN 1 THEN 'U_'  ELSE '' END 
            END + CASE has_filter WHEN 1 THEN 'F_'  ELSE '' END
            + LEFT(REPLACE(OBJECT_NAME(i.[object_id]), '''', ''), @TableLimit) 
            + '_' + STUFF((SELECT '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit)
                FROM sys.columns AS c 
                    INNER JOIN sys.index_columns AS ic
                    ON ic.column_id = c.column_id
                    AND ic.[object_id] = c.[object_id]
                WHERE ic.[object_id] = i.[object_id] 
                AND ic.index_id = i.index_id
                AND is_included_column = 0
                ORDER BY ic.index_column_id FOR XML PATH(''), 
                TYPE).value('.', 'nvarchar(max)'), 1, 1, '') +''';'
        FROM sys.indexes AS i
        WHERE index_id > 0 AND is_primary_key = 0 AND type IN (1,2)
        AND OBJECTPROPERTY(i.[object_id], 'IsMsShipped') = 0;
    END


    IF @DefaultConstraints = 1
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- DefaultConstraints ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
            + REPLACE(dc.name, '''', '''''') + ''', @newname = N''DF_' 
            + LEFT(REPLACE(OBJECT_NAME(dc.parent_object_id), '''',''), @TableLimit)
            + '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit) + ''';'
        FROM sys.default_constraints AS dc
        INNER JOIN sys.columns AS c
        ON dc.parent_object_id = c.[object_id]
        AND dc.parent_column_id = c.column_id
        AND dc.is_ms_shipped = 0;
    END


    IF @CheckConstraints = 1
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- CheckConstraints ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
            + REPLACE(cc.name, '''', '''''') + ''', @newname = N''CK_' 
            + LEFT(REPLACE(OBJECT_NAME(cc.parent_object_id), '''',''), @TableLimit)
            + '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit) + ''';'
        FROM sys.check_constraints AS cc
        INNER JOIN sys.columns AS c
        ON cc.parent_object_id = c.[object_id]
        AND cc.parent_column_id = c.column_id
        AND cc.is_ms_shipped = 0;
    END


    SELECT @sql;


    IF @SELECT_Only = 0 AND @sql > N''
    BEGIN
        EXEC sp_executesql @sql;
    END
END