次の方法で共有

Best Practice: Naming Constraints

  • [アーティクル]

Within SQL Server there are a number of different types of constraints:

  • CHECK
  • DEFAULT
  • FOREIGN KEY
  • PRIMARY KEY
  • UNIQUE

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.

Comments

  • Anonymous
    November 01, 2009
    An interesting approach to getting added value out of constraint naming is to let them describe "what went wrong" in a meaningful way. This makes interpreting error messages and traces that much easier. For example, instead of "FK_persons_country" we could say "PersonsCountryExists"

  • Anonymous
    November 02, 2009
    As always there's the 5% exception. We had a large table that was updated weekly with a large number of inserts/updates. The fastest way to do this was with a Select Into. Subsequently the old table was renamed and the new table was renamed before next week's run. A DBA-named constraint on both tables would yield an error for duplicate names. The default name would never clash and would drop away when the old table was eventually dropped.

  • Anonymous
    October 22, 2010
    sir i was told to name constraints so dat u can drop with minimum efforts. great artical sir but i didnt got "will differ betwween systems".if a script is executed in two different computers and system is supposed to generate the name for constraints... so constraint names will be different..thats what you are trying to tell sir???