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).

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, T-SQL