Tag: SQLNewBlogger

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.

Designating a Primary Key at Design Time

In the first post on this subject, I noted that I often add a primary key after the fact, using ALTER TABLE. However I’ve been wanting to build the habit of adding the PK at design time, inline with the CREATE TABLE statement, so I decided to look up the syntax and start practicing.

The inline code adds a CONSTRAINT clause into the table definition. I can do this in two ways, the most common is after I’ve built all the columns, I add CONSTRAINT, a name (PKUsers) then the PRIMARY KEY keyword with the optional clustered/nonclustered designation, and finally the column(s) in parentheses

Here’s my new code:

The better way, IMHO, is to do this inline if you have one column. I know this isn’t consistent, but I can easily see the constraint this way.

This is more difficult to see if you have multiple columns, but you can do this:

Simple and easy to find the PK now, it’s named correctly, and it’s easy to read.

References

A few places I used

• https://msdn.microsoft.com/en-us/library/ms189039.aspx

Quick and Easy Blogging

This post occurred to me while I was writing my first post.  Actually, this occurred to me first, but I realized that I often do the ALTER method in post 1, so I wrote that first.

Changing to this post required using the same MSDN article, dropping the table, rewriting the DDL code, and testing it. About 5 minutes for this one because it was based on a previous post.

Look for the other posts in the April challenge.

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.