Constraint names, Say NO to the default

12

January 21, 2015 by Kenneth Fisher

Have you ever seen a constraint with a name like PK__TableNam__EA185FBF8FF1529D? It’s kind of funny looking right? I mean it makes no difference in terms of how things run but it does make it a bit of a pain when you need to code with it. Say dropping the PRIMARY KEY constraint before adding a column to it. So what causes names like this to show up in your object list?

CREATE TABLE TableName (
	TableName_Id int NOT NULL PRIMARY KEY
	,TableName_Code char(1) CHECK (TableName_Code IN ('A','B','C'))
	,TableName_Name varchar(50) DEFAULT ('')
);

The syntax for creating constraints is pretty simple. If you want a column to be the PRIMARY KEY just say PRIMARY KEY, if you want a CHECK constraint just say CHECK add a couple of ()s with a condition inside them, and if you want a DEFAULT for a column just say DEFAULT and add the ()s with your default value inside.

Unfortunately then you get this:

SELECT object_name(constid) 
FROM sys.sysconstraints
WHERE id = OBJECT_ID('TableName');

DefaultConstraintNames1

On the up side they have PK, CK and DF in front of them, they even have part of the table name. On the down side, that is about as much information as you are going to get. SQL also put’s a random string at the end of the name in order to keep it unique. Necessary, of course, when you have a computer generated name. It is, however, a royal pain when you try to type it out.

So what’s the solution?

Don’t use a computer generated name. The syntax to include the constraint name is just as simple as adding the constraint in the first place.

-- Drop the old version of the table
DROP TABLE TableName;
-- Create the new version of the table
-- with actual constraint names.
CREATE TABLE TableName (
	TableName_Id int NOT NULL CONSTRAINT pk_TableName PRIMARY KEY
	,TableName_Code char(1) CONSTRAINT ck_TableName__TableName_Code 
			CHECK (TableName_Code IN ('A','B','C'))
	,TableName_Name varchar(50) CONSTRAINT df_TableName__TableName_Name 
			DEFAULT ('')
);

All I did was add CONSTRAINT ConstraintName right before each of the constraints. Now I have a list of constraints that’s easy to understand, easy to type, and has whatever information I choose to put in it. In this case I put the constraint type, then the table name (that’s it for a PK) and the column name. I could add more information if I wanted but that seemed like enough in this case. It’s also easier to type. Now you do have to make sure you keep your names unique but I don’t really see that as a big issue personally.

SELECT object_name(constid) 
FROM sys.sysconstraints
WHERE id = OBJECT_ID('TableName');

DefaultConstraintNames2

But what is the solution if the constraints were already created and you want to re-name them to make your lives easier?

EXEC sp_rename 'PK__TableNam__EA185FBF5DD7C206','pk_TableName';
EXEC sp_rename 'CK__TableName__Table__658C0CBD','ck_TableName__TableName_Code';
EXEC sp_rename 'DF__TableName__Table__668030F6','df_TableName__TableName_Name';

Just a simple sp_rename command and you are done. You could even generate part of the command using dynamic SQL.

SELECT 'EXEC sp_rename ''' + object_name(constid) + ''','''';'
FROM sys.sysconstraints
WHERE id = OBJECT_ID('TableName');

So what’s the point of all this? Adding proper constraint names is easy. It also helps to keep the next guy down the line from cursing your name. Please make it a habit (says the guy dealing with a bunch of default names right now).

12 thoughts on “Constraint names, Say NO to the default

  1. Excellent. This is getting added into my next class. Thank you.

  2. Charles Kincaid says:

    Great article. We had made this part of our design policy. Since most of our folks were still designing tables in the SSMS designer we had taken to adding all the constraints after the table was created.

    While DF_TableName_ColumnName is OK check constraints are even more important. SQL server will tell you when an INSERT or UPDATE violates a check constraint and the auto generated names are no help in identifying the problem. Try CK_TableName_Purpose

    • Thanks! Good comment. Using CK_TableName_Purpose is a good example of a check constraint name. The biggest thing is going to be making sure your names are readable, typeable and if possible have some level of meaning.

  3. Jeff Moden says:

    Great article, Kenneth.

    Just to add to your fine article, there is one exception where you really should allow the system to build constraint names and that’s if the constraints are on Temp Tables. Constraint names must be unique in the database that they’re used in. If you hard code a constraint name for a Temp Table, then concurrency of multiple runs will be destroyed. Unless there’s something really tricky that needs to be done with the constraints on Temp Tables, my recommendation would be to always (don’t get a chance to say that word often) allow the system to name constraints on Temp Tables.

    –Jeff Moden

    • Excellent point. My first thought was that you should use named constraints even in temp tables just to make sure you keep the habit up. But then you mentioned the uniqueness point and I realized you are absolutely correct. I’ve actually run into that before. It’s one of those things I do without thinking about it and so I didn’t think to put it into my post. Thanks for the compliments and for bring it up!

  4. paschott says:

    Definitely worth implementing if you just skip over naming your constraints, but I want to bring up one possible gotcha. There’s a “system named” flag that goes behind this and is used by some tools to determine whether the name was auto-generated or not. In SQL 2005, you can rename the constraint but this does not clear out the “System Named” flag so some comparison tools will then pick up this constraint as different and try to drop/re-create it. Once that’s done, it behaves normally but can be a major pain if it’s the PK constraint on a large table.

    This apparently was fixed in SQL 2008 and later so running an sp_rename clears that “system named” flag appropriately, but if you still have 2005 servers this could bite you.

  5. mjswart says:

    Very cool. I wonder if it would be worth suggesting to Microsoft that enforcible policy should be available to prevent these generated constraint names.

    • Glad you liked it. I’m always up for adding new options to PBM. Although as Jeff mentioned you would want to be able to control it to a certain extent to exclude things like temporary tables.

  6. […] was reading Kenneth Fisher’s piece on constraints recently and was struck by the fact that this is one of those small changes you make to your […]

  7. Great post! With your naming convention (ConstraintType_TableName_Column) you wouldn’t be violating the unique identifiers in user tables as well. Unless you create the same temp table twice in different scopes 🙂

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: