New Blogger Challenge 1 – Adding a Primary Key

The April Blogger Challenge is from Ed Leighton-Dick and aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

Primary Keys

I firmly believe that every table should have a primary key. At least until you have a reason not to have one. If you have a reason, fine, but if you can’t explain it or convince me, then just add a primary key.

I have tended to build tables like this:

CREATE TABLE Users
(
  MyID int IDENTITY(1, 1)
, firstname varchar(250)
, lastname varchar(250)
, gender char(1)
, postalcode varchar(12)
, contactphone varchar(12)
);
GO
ALTER TABLE Users ADD PRIMARY KEY (MyID);

Lately I’ve not liked that as my primary key now has a name like [PK__Users__7131A74146D2BBC1]. I’d rather have a more organized database with a touch more effort.

The better way to add the key later is like this:

ALTER TABLE dbo.Users 
  ADD CONSTRAINT pkUsers PRIMARY KEY (MyID);

This way I can name the key, and I specifically note this is a constraint, and with the PRIMARY KEY option, it’s a unique constraint.

References

A few places I searched around to double check myself.

Quick and Easy Blogging

This post occurred to me while I was writing some code. I mocked up a table in about 2 minutes, and then ran a quick search on the Internet. Reading a few links was about 10 minutes and then testing the code (including dropping the table and recreating it a few times) was less than 5 minutes. All told, I solidified some knowledge and completed this in about 20 minutes. I also have drafts and ideas from this post for 2 other posts that cover this same topic in a similar way.

Look for the other posts in the April challenge.

About way0utwest

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

4 Responses to New Blogger Challenge 1 – Adding a Primary Key

  1. I’ve seen both methods used. The other method I’ve seen used is to declare the PK right on the column in the table definition.

    As much as you may like naming our constraints I have found them to be somewhat annoying. Here is why….

    I am dealing with code written by a contractor who uses this construct on temporary tables when developing result sets for reports. This developer was aware enough to check for the existence of the table before generating the new table. However he never checked for the name constraint.

    The way I resolved this issue was to declare the constraint on the PK column. This way I never had an issue and the code would always run. The named constraint would sometimes cause an error since it was never checked for existence and dropped prior to creating the table. This would typically occur if a end user decided to re-run the report.

    I can see that if the tables are standard core tables of a database design then I see no problems naming the constraint.

    Keep in mind that habits of developers who may not be 100% on the ball. They will typically design/create tables the same way if they are core tables of a system or temporary tables being used to produce a result set for a report or screen display.

    Thanks for sharing.
    Kurt

    • way0utwest says:

      I’d argue that you should be checking for constraint names, but more importantly, have a good standard in place for your system.

      I’ll do inline in the next challenge post 😉

  2. Pingback: April Blogging Challenge 2 – Primary Key in CREATE TABLE | Voice of the DBA

  3. Pingback: SQL New Blogger Digest – Week 2 | The Rest is Just Code

Comments are closed.