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.
