Constraint names, Say NO to the default
14January 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');
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');
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).
SQL Studies 
Excellent. This is getting added into my next class. Thank you.
Awesome. Glad you liked it 🙂
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.
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!
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.
Interesting. I’ve never run into that before. Thanks for the warning!
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.
[…] 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 […]
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 🙂
[…] Names for constraints are optional meaning that if you don’t provide a name when it’s created or cannot afford one, one will be appointed to you by the system. These system provided names are messy things and I don’t think I have to discourage you from using them. Kenneth Fisher has already done that in Constraint names, Say NO to the default. […]
[…] mentioned a while back that you should avoid the default names for constraints. This is one case where you absolutely should not. Always take the default name for a constraint […]