I'm not a fan of letting the system automatically name constraints, so that always leads me to thinking about what names I really want to use. System-generated names aren't very helpful.
Primary keys are easy. There is a pretty much unwritten rule that SQL Server people mostly name them after the table name. For example, if we say:
A violation of the constraint will return a message like:
The name isn't helpful and it shows us the key value but not which column was involved.
So, we might say:
Even in this case, there are a few questions:
- Should the name include the schema? (ie: PK_dbo_Clients) If not, this scheme has an issue if there are two or more tables in different schemas with the same table name.
- Should the name include the columns that make up the key? (ie: PK_dbo_Clients_ClientID) This might be useful when an error message is returned. A message that says that you violated the primary key, doesn't tell you which column (or columns) were involved.
So perhaps we're better off with:
I do like to name DEFAULT constraints in a similar consistent way. In theory it doesn't matter what you call the constraint however, if I want to drop a column, I first have to drop the constraint. That's much easier if I have consistently named them. I don't then have to write a query to find the constraint name before I drop it. I include the schema, table, and column names in the DEFAULT constraint as it must be unique within the database anyway:
CHECK constraints (and UNIQUE constraints) are more interesting. Consider the following constraint:
The error returned is:
Note how (relatively) useless this is for the user. We could have named the constraint like so:
Note how much more useful the error becomes:
And if we are very keen, we might remove the underscores and delimit the name to make it more readable:
This would return:
I'd like to hear your thoughts on this. How do you name your constraints?
Hi Greg
You are right if we need to drop a constraint we do not have to write a query to find it. But how often we drop PK/Check constraints on the system?
If I create a table I do name constraints but do not really dictate developers do it especially if we create a temporary table within a stored procedure 🙂
Hi Greg,
I'm a big fan of naming check constraints with descriptive text, and just as shown in your last example quoted identifiers so I can use spaces. It's a really great way of creating human-readable errors and saves lots of time. But there is no need to put the CK or the table/schema in there. All of that information is already in the error message.
–Adam
I came across an issue where I had to shuffle tables around because they were in the incorrect filegroups. For some, this involved recreating the tables due to having BLOB data types, which in turn involved fiddling with the constraints. I did this in a DEV environment which was essentially just empty tables. Once I had generated the scripts required, I recreated my DEV environment by dropping the database & recreating it via scripting. This is when I discovered that the original developer had not explicitly named all the constraints (they'd done most but not all) – my script was generating errors due to not being able to find the constraints I'd specified. This meant I had to audit the remaining pre-prod and prod environments to get the system-generated constraint names and ended up having a script for each environment. Annoying.
Great article! I am updating my in-draft naming standards with these suggestions. And thanks, Adam, for pointing out that the table name is included in the error message of a check constraint. Including the table name may be redundant but ensure a unique name?
I agree it is a good practice to name constraints. Think of a production environment where you have to deliver scripts to a team or someone in charge of the delivery processes, the names will be the same across the different environments and it makes things easier
I like this idea.
Is there a way to provide these messages in multiple languages though?
What's about this syntax:
ALTER TABLE [dbo].[Roles2016.UsersCRM] WITH CHECK ADD CHECK?(([Estado]=(4) OR [Estado]=(3) OR [Estado]=(2) OR [Estado]=(1)))
for set a _name to constraint_ (***CK_…***