THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Naming CHECK and UNIQUE Constraints

This blog has moved! You can find this content at the following new location:

http://blog.greglow.com/index.php/2014/11/13/naming-check-and-unique-constraints/

Published Wednesday, November 12, 2014 2:12 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

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

November 12, 2014 1:35 AM
 

Anonymous said:

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

November 12, 2014 1:20 PM
 

Scott Duncan said:

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.

November 12, 2014 1:34 PM
 

Lee Anne Pedersen said:

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?

November 12, 2014 2:05 PM
 

Thomas said:

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

November 17, 2014 11:46 AM
 

JohnN said:

I like this idea.

Is there a way to provide these messages in multiple languages though?

December 12, 2014 4:10 AM
 

kiquenet said:

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_...***

July 7, 2016 5:59 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement