Within SQL Server there are a number of different types of constraints:
- FOREIGN KEY
- PRIMARY KEY
The reference for CREATE TABLE can be found here.
Now what’s interesting about constraints is when creating a constraint the name is optional. If you don’t provide a name the system will provide one for you. This is sort of like if you cannot afford an attorney one will be provided by the court. The syntax for DEFAULT looks like this:
[ CONSTRAINT constraint_name ] DEFAULT constant_expression
The ‘[‘ ‘]’ around “CONSTRAINT constrain_name” mean the statement is optional.
It’s very nice of the system to provide the constraint name on your behalf but just like anyone in their right mind would hire their own lawyer – assuming they can afford it, etc – any developer in their right mind should provide their own constraint name. Why you ask. The answer is obvious, the constraint is part of your application and since the system provides control over the name you should exercise that right.
The syntax to name a constraint is simple and only requires two additional entries over the syntax for system generated names. Said another way, don’t give me the lazy excuse that allowing the system to name your constraints saves you time. In fact, this might become part of my standard arsenal of DBA interview questions: do you name your constraints? Or something like that.
Let’s take a closer look at the differences. Just for fun I’ll use a DEFAULT constraint in my example.
System Named Constraint
CREATE TABLE dbo.SystemNamedConstraint ( column1 INT DEFAULT 42 )
The result of the above will differ between systems but in my case I ended up with a constraint on the table named: DF_SystemNam_colum_07020F21. Not overly friendly.
Programmer Named Constraint
CREATE TABLE dbo.ProgrammerNamedConstraint ( column1 INT CONSTRAINT DF_column1 DEFAULT 42 )
The result of the above won’t differ between systems and as you expect you’ll end up with a constraint on the table named: DF_column1.
You’ll notice I used DF as the prefix on my constraint name. Mainstream abbreviations include:
- CHECK: CK
- DEFAULT: DF
- FOREIGN KEY: FK
- PRIMARY KEY: PK
- UNIQUE: UQ
There are variants to these for things like CLUSTERED (CL or in the case of a PRIMARY KEY – PKCL). But there’s no reason you couldn’t come up with your own standard.
The bottom line is don’t be a lazy DBA/Programmer and let the system name your constraints. The few extra taps on the keyboard will be worth it in the long run.