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.
- https://msdn.microsoft.com/en-us/library/ms189039.aspx
- http://stackoverflow.com/questions/11794659/add-primary-key-to-existing-table
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.
