DevOps–Fixing Poorly Named Constraints

I was building some code the other day and kept getting problems in my deployment for a change. The deployment was having issues, and this came down to this statement.

ALTER TABLE EventLogger DROP CONSTRAINT [PK__EventLog__5E548648B043C0BC]

The problem was that this was the constraint on one developer’s workstation, but on another laptop, and in QA/Staging/Production, this constraint didn’t exist.

When we deploy to other environments, such as QA and Production, we will always see the wrong constraint, as most deployment mechanisms look at the name of the object, not the function. Every upgrade script will typically try to run the above statement and then run an ALTER TABLE ADD CONSTRAINT later to add the PK back.

If we have the correct name of the constraint in QA, the script will work. However, the name is likely different in each environment, so we need to fix this.

We can find the name of the PK with this script:

SELECT 
    A.TABLE_NAME, 
    A.CONSTRAINT_NAME, 
    B.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
       CONSTRAINT_TYPE = 'PRIMARY KEY' 
    AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.TABLE_NAME = ‘EventLogger’

If we take the results of this, we can use this to produce a drop script. Here’s one way to do this. We’ll store the name of the constraint in a variable and use the EXEC() statement to execute some dynamic SQL. We then can execute the ADD CONSTRAINT with a new name later in the script.

DECLARE @s VARCHAR(200)
SELECT @s = A.CONSTRAINT_NAME
FROM 
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, 
     INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
        CONSTRAINT_TYPE = 'PRIMARY KEY' 
     AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND A.TABLE_NAME = 'EventLogger'

EXEC('alter table EventLogger drop constraint ' + @s)
GO
/*
Other work
*/
ALTER TABLE dbo.EventLogger ADD CONSTRAINT EventLoggerPK PRIMARY KEY (LogId)

This is the type of DevOps change that I would release a table at a time, slowly cleaning up the constraint names. This will smooth your process and increase the reliability of your deployments.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

2 Responses to DevOps–Fixing Poorly Named Constraints

  1. Michael Jimmerson says:

    For PK’s, the assumption here is there is no FK relationship to use this. Otherwise the PK would not drop. Should use sp_rename instead of dropping the constraint.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s