Adding the Constraint Name to the PK at the End of Create Table–#SQLNewBlogger

Adding the Constraint Name to the PK at the End of Create Table–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

A good habit to get into is to explicitly name your constraints. I try to do this when I create tables to be sure that a) I have a PK and b) it’s named the same for all environments.

I can create a PK inline, with a simple table like this:

CREATE TABLE Batting
   (
        BattingKey INT NOT NULL CONSTRAINT BattingPK PRIMARY KEY
        , PlayerID INT
        , BattingDate DATETIME
        , AB TINYINT
        , H TINYINT
        , HR tinyint
   )

;

This gives a primary key, named “BattingPK, that I can easily see inline with the column.

Not everyone likes this, and I do run into clients and customers that want the keys separated from the column. This is fine, and I understand that this explicitly calls out the keys separately from the column.

This is an easy change to my code.  I move the CONSTRAINT part to the end, as a separate item in the column list, and add the column(s) that I want to use in the constraint.

CREATE TABLE Batting
   (
        BattingKey INT NOT NULL 
        , PlayerID INT
        , BattingDate DATETIME
        , AB TINYINT
        , H TINYINT
        , HR TINYINT
        , CONSTRAINT BattingPK PRIMARY KEY (BattingKey)
   )

;

As you can see, inlining names for constraints is pretty easy, and it’s a good practice to get in the habit of adopting.

If I didn’t do this, I’d get a system generated name, which is fine, but the constraint name would then be different on every system where I deployed this object. Since I often want to test something on one system and deploy on another, future coding gets much more complex than it is by just doing this from the start.

SQLNewBlogger

This was a quick 5 minute post for me, following a short session teaching a client how to add the constraint to their table code.