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.
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.
ReplyDeleteThanks Dennis, I knew that this framework exists, but never had opportunity to have a closer look at it. I will give it a try :)
ReplyDeleteHey Piotr,
ReplyDeleteI 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
Thanks Saggi, this is clever idea.
ReplyDelete