Sunday, June 13, 2010

Coding practices - Call your constraints

I wrote a few weeks ago about unit tests and good practice of naming your constraints. I think that most of the developers are used to call primary keys and unique constraints, sometimes foreign keys. But in most of the code I’ve seen the CHECK and DEFAULT constraints remain unnamed. You might wonder, why bother? The answer is that as long as you don’t have to drop the constraints, everything is OK – apart from cryptic error messages if the constraints are violated. The problem begins if for some reason you need to drop the constraint – for example to change range of the allowed values or default value of a column. The other scenario is also painful – if you want to compare two databases and you script out objects with constraint names. If you let SQL Server to name your constraints, each database you create from your scripts is different. And you have different error messages in production than in UAT for example. Not a very good idea for troubleshooting. Let’s suppose you create a table for storing order summaries.
   1: create table OrderSummary
   2: (
   3: OrderID int not null,
   4: OrderValue decimal(10,2) not null,
   5: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),
   6: OrderDate datetime not null default getdate()
   7: )
Looks nice. But to drop the constraint on the OrderCurrency column, you have to use dynamic sql:
   1: select * from sys.check_constraints
   2: declare @sql nvarchar(200)
   3: select @sql = N'alter table OrderSummary drop constraint ' + 
   4:         quotename((select name from sys.check_constraints 
   5:             where parent_object_id = object_id('OrderSummary')))
   7: --print @sql
   8: exec sp_executesql @sql
Please note that the above script works only if you have one unnamed check constraint. If you have more constraints, you have to write more complex code. Not a very nice option if you want to have some control on which constraints you want to drop and which are to stay. Now if you run the following script, you will see that each time you create the table, names of constraints are different:
   1: use master
   2: go 
   3: create database devDB
   4: go
   5: use devDB
   6: go
   7: --simulate various test objects creating and dropping in DEV database
   8: create table OrderSummaryTest
   9: (
  10: OrderID int not null,
  11: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),
  12: OrderDate datetime not null default getdate()
  13: )
  14: drop table OrderSummaryTest
  16: go
  18: create table OrderSummary
  19: (
  20: OrderID int not null,
  21: OrderValue decimal(10,2) not null,
  22: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),
  23: OrderDate datetime not null default getdate()
  24: )
  25: go
  27: select convert(varchar(10), db_name()) [test], 
  28:     name from sys.check_constraints 
  29:     where parent_object_id = object_id('OrderSummary')
  31: go
  33: create database prodDB
  35: go
  37: use prodDB
  39: go
  41: create table OrderSummary
  42: (
  43: OrderID int not null,
  44: OrderValue decimal(10,2) not null,
  45: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),
  46: OrderDate datetime not null default getdate()
  47: )
  49: go
  51: select convert(varchar(10), db_name()) [test], 
  52:     name from sys.check_constraints 
  53:     where parent_object_id = object_id('OrderSummary')
  55: go
  57: ----cleanup
  58: use master
  59: go
  60: drop database devDB
  61: drop database prodDB
  62: go
As you see, the constraint names are different and UGLY. Note that I simulated object creation and destruction in devDB, because if you comment lines 7 – 14, you may get the same object ids for the constraints. But this never happens in real life. If you want to avoid such problems and make your and your dba’s life easier, give your constraints names, like in the following script:
   1: create table OrderSummary
   2: (
   3: OrderID int not null,
   4: OrderValue decimal(10,2) not null,
   5: OrderCurrency char(3) constraint CHK_OrderCurrencyRange check (OrderCurrency in ('EUR', 'USD')),
   6: OrderDate datetime not null constraint DF_OrderDate default getdate()
   7: )
This way, to drop the default constraint you just have to call:
   1: alter table OrderSummary drop constraint [DF_OrderDate]
No dynamic SQL required, and code is 100% same in production and in DEV. To sum it up, if you get the habit of assigning names to ALL your constraints, your code will look better and others will be able to understand it faster, what is important when troubleshooting production issues.


  1. Good post Piotr - I completely agree, not naming constraints causes a lot of little annoyances. It even makes it rather difficult to compare two databases (say a model and a production database) to see if they have the same schema. I noticed in your other post, "Good practices - database programming, unit testing" you were also testing your constraints using your own hand-rolled testing techniques. You may be interested to know about the tSQLt unit testing framework. It lets you do all your unit testing in TSQL (, but provides the nice framework pieces that are missing from hand-rolling your own.

  2. Thanks Dennis, I knew that this framework exists, but never had opportunity to have a closer look at it. I will give it a try :)

  3. Hey Piotr,

    I agree that fetching constraint names in order to alter or drop them (usually when you want to alter a column that is constrained by them) is an annoyance. I wrote this little script for a customer who wanted to rename existing default constraints (he didn't have any check constraints) to reasonable names:

    DECLARE @RenameCommand NVARCHAR(MAX)

    SELECT RenameCommand = 'exec sp_rename ' + QUOTENAME(DefaultName,'''') + ' , ' + QUOTENAME('DF_' + TableName + '_' + ColumnName,'''')
    FROM (
    DefaultName =
    , ColumnName =
    , DefaultDefinition = dc.definition
    , TableName = OBJECT_NAME(c.object_id)
    , SchemaName = OBJECT_SCHEMA_NAME(c.object_id)
    FROM sys.default_constraints dc
    INNER JOIN sys.columns c ON
    dc.parent_column_id = c.column_id
    AND dc.parent_object_id = c.object_id
    INNER JOIN sys.tables tbl ON
    tbl.object_id = c.object_id
    when tbl.is_ms_shipped = 1 then 1
    when (
    major_id = tbl.object_id and
    minor_id = 0 and
    class = 1 and
    name = N'microsoft_database_tools_support')
    is not null then 1
    when OBJECTPROPERTYEX(tbl.object_id,'IsSystemTable')=1 then 1
    else 0
    end AS bit)=0
    ) c

    OPEN z;

    WHILE 1=1
    FETCH NEXT FROM z INTO @RenameCommand

    PRINT @RenameCommand

    CLOSE z;


    S. Neumann

  4. Thanks Saggi, this is clever idea.