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')))
   6:  
   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
  15:  
  16: go
  17:  
  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
  26:  
  27: select convert(varchar(10), db_name()) [test], 
  28:     name from sys.check_constraints 
  29:     where parent_object_id = object_id('OrderSummary')
  30:  
  31: go
  32:  
  33: create database prodDB
  34:  
  35: go
  36:  
  37: use prodDB
  38:  
  39: go
  40:  
  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: )
  48:  
  49: go
  50:  
  51: select convert(varchar(10), db_name()) [test], 
  52:     name from sys.check_constraints 
  53:     where parent_object_id = object_id('OrderSummary')
  54:  
  55: go
  56:  
  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.

4 comments:

  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 (http://www.tsqlt.org), but provides the nice framework pieces that are missing from hand-rolling your own.

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

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

    DECLARE z CURSOR LOCAL FAST_FORWARD FOR
    SELECT RenameCommand = 'exec sp_rename ' + QUOTENAME(DefaultName,'''') + ' , ' + QUOTENAME('DF_' + TableName + '_' + ColumnName,'''')
    FROM (
    SELECT
    DefaultName = dc.name
    , ColumnName = c.name
    , 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
    WHERE CAST(
    case
    when tbl.is_ms_shipped = 1 then 1
    when (
    select
    major_id
    from
    sys.extended_properties
    where
    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
    BEGIN
    FETCH NEXT FROM z INTO @RenameCommand
    IF @@FETCH_STATUS!=0
    BREAK;

    PRINT @RenameCommand
    EXEC(@RenameCommand)
    END

    CLOSE z;
    DEALLOCATE z;


    ----

    Cheers,
    S. Neumann

    ReplyDelete
  4. Thanks Saggi, this is clever idea.

    ReplyDelete