Wednesday, May 28, 2008

Name Table Constraints

When creating constraints, NAME THEM !

For example ;
ALTER TABLE [sales].[orders]
ADD [tax] DECIMAL(2,2) NOT NULL DEFAULT ((17.5))

Gives a randomly named constraint >

DF__Orders__Tax__42E0813E

Not a great problem in itself, it's not like you'll need to look at the name of the constraint is it?

WRONG!
Comparison tools (in my case SQL Compare) will compare otherwise identical tables and report differences.

It is far safer therefore, to explicitly name the constraint -
ALTER TABLE [sales].[orders]
ADD [tax] DECIMAL(2,2) NOT NULL CONSTRAINT [DF_tax] DEFAULT ((17.5))

Further Reading -
http://www.sqlteam.com/article/default-constraint-names

No comments: