SQL 102 – Primary Keys – shannonlowder.com

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.

You can create a primary key at the same time you create the table.

CREATE TABLE inventory
(
   id INT IDENTITY(1,1) PRIMARY KEY,
   product VARCHAR(50) UNIQUE,
   quantity INT,
   price DECIMAL(18,2)
)
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?
ALTER TABLE tableName
 ADD CONSTRAINT pk_PkName PRIMARY KEY (columnName)

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.

Leave a Reply Cancel reply