I’ve run into an annoying problem with SQL Server: it generates names for the default constraints, allowing you to shoot yourself in the foot. Kind of C++.
The problem: we must manage the database schema for an application. The script to create the schema contains a statement like following:
create table A
( Idn integer identity(1,1) not null primary key,
FieldA nvarchar(100) not null,
ServerTimestamp binary(8) not null default (0x00)
)

Everything looks ok, right?
Nope, there’s a hidden bug in there.
The problem is with the way SQL Server generates the constraint name; there’s no guarantee that, if you run this script on two different machines, the constraint names will be the same. If you have to manipulate the constraints later, then you will have a problem.

As a best practice, to avoid such situations, always name your constraints. The syntax is simple:
create table A
( Idn integer identity(1,1) not null primary key,
FieldA nvarchar(100) not null,
ServerTimestamp binary(8) not null constraint SERVERTIMESTAMPDEFAULTCONSTRAINT default (0x00)
)

Now this problem reminds me of the time when I was learning C/C++ and I was reading how not to shoot myself in the foot (‘Writing solid code’ and ‘Code complete’ were the best books at the time). I still wonder today why the language designers allow the users (developers) to shoot themselves in the foot. The answer is pretty clear: if you do not allow a developer to shoot himself in the foot, he will not use your rifle. Because the majority of us prefer to use a rifle that works fast most of the time and accept the tradeoff that, from time to time, we’ll need band-aids and maybe a crutch.

Advertisements