Adding a primary key to a table

A properly designed table (in a relational database) will always have a primary key. This is essential to uniquely identify records and to relate to other tables.

Unfortunately there are a lot of databases with tables without a primary key. The good news is that it is very easy to correct. You can either add a new column with a primary key constraint or you can add a primary key constraint to an existing column.

Add a column called id to the table and set it as the primary key

Let's say we have an employee table like this:

We are going to add a column named id and set it as a primary key. The id column will be numbered automatically. Please note that the SQL syntax for Microsoft SQL Server differs from the syntax for MySQL and PostgreSQL. The queries are as follows:

Microsoft SQL Server 2008:

MySQL & PostgreSQL:

The amended employee table will look like this:

Add a primary key constraint to an existing column

A primary constraint can only be added to a column if:

1.            There are only unique values in the column; and

2.            The column is not able to contain NULL values.

If your table looked like this (but id were not a primary key) 

you can execute the following query to add a primary key constraint: