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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

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

  1. Pingback: Adding Constraints In The CREATE TABLE Statement – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.