SQL 102 – Primary Keys

In SQL 101, I showed you how to create a table. I want to build on that today by introducing the concept of a primary key. A primary key is a column, or combination of columns that uniquely identify the row in the table. By doing this you can have some assurance that the row is unique. While every other column in the table could be exactly the same, once you identify a primary key for a table, you cannot have more that one record with that value for a primary key.

This comes in very useful when you need to remove duplicate data. You’ll be able to call the one primary key that you want to delete, and you know it will only remove one row, not both rows.

In this example I’m creating a simple primary key. It’s an integer. IDENTITY is a system function that can assure each time a record is inserted, a new value will be created.

IDENTITY(seed, increment)

The seed is the initial value you will use for the first record. Increment is how much you want to add to the first value for each value after the first. In my first example, you see I start with 1, and for each record after that I’ll add one to it, to come up with the new value.

The last part of the id column line, you see I included the reserved words PRIMARY KEY. That’s all you have to do to indicate this column is your primary key. After you create the table, you won’t be able to insert two records with an ID value of 1, or any other integer. You’re protected.

Now, what if you have a table that doesn’t have a primary key? You can alter the table and add a column to serve as your primary key.

ALTER TABLE tableName ADD id INT IDENTITY(1,1) PRIMARY KEY

That will work, so long as you don’t already have a primary key defined, and the column id is not already in use for that table. What if you wanted to use an existing column, and make it the primary key?

This will work, so long as the columnName you choose is in the tableName you indicate. I’d also pick something more descriptive than pk_pkName for the name of your primary key, but that’s completely up to you, Administrator!

Well, those are the basics. Pretty straightforward, as long as you understand the concept of a primary key. Hopefully I explained that well enough you can use this new lesson. If you have any questions, comments, concerns, please… let me know. I’m here to make sure you learn anything you need for Microsoft SQL.

Shannon Lowder is the Database Engineer you've been looking for!
Look no further for expertise in:
Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.