How To Add Primary Key To Existing Table In SQL

Introduction

In this article, we will learn how to add primary key to existing tables in SQL Server. Here we will use SQL Server as our database. SQL Server is a relational database management system. A primary key constraint creates a unique identifier for each row in a table. This is usually done using an auto-incrementing identity column.

ALTER TABLE SQL statement can be used to alter a table that includes adding a primary key to an existing table. In this example, we are going to create a database table called Books without a primary key and then we will add a primary key to the table using the ALTER TABLE SQL statement.

What is a primary key

A primary key is a unique identifier that uniquely identifies each row in a table.

A primary key consists of one or more columns and must be unique for each row in the table.

The column(s) that make up the primary key cannot contain null values, but all other columns can contain null values.

Why do we need a primary key

A primary key is one or more columns that uniquely identify each table record. Because the values in a primary key are unique, you can use it to identify and retrieve records faster than when using other column(s). A table with a primary key allows you to maintain data integrity by ensuring that the data in your tables do not include duplicate records.
A primary key also helps to improve performance as it provides an easy way for SQL Server to locate specific records when it processes queries. For example, if you want information about a particular order item, SQL Server will have no problem finding this data because of its unique identifier.
A primary key constraint is a unique identifier that's used to identify each row in a table. It also ensures that no other row in the table has the same primary key value. With a primary key constraint, you can't delete or update rows unless there are no remaining rows with duplicate values for the column being modified.
A good way to think about this is like when you go through security at an airport and they ask you which bag belongs to whom—the ID number acts as a unique identifier so they know which bag belongs to which traveler.
Primary keys are used for two main purposes: data integrity and referential integrity (RI). Data integrity means that your data will be accurate because it can't be changed without affecting something else; RI ensures that related tables have matching field names so that if one thing changes, everything else associated with it changes too.

Create a table without a primary key

Here is a database table name “Books”. This table will store all detail of the books including title, author, description, etc. The table columns are Id, Title, Author, Description, Publisher, Price, CreatedDate, YearPublish, LastUpdatedDate.

The following SQL query creates a table named Books with above mentioned columns.

Create table Books(
    Id int not null,
    Title varchar(50) not null,
    Author varchar(50),
    Description varchar(200),
    Publisher varchar(50),
    Price DECIMAL(10,2),
    CreatedDate datetime, 
    YearPublished datetime,
    LastUpdatedDate datetime
)

The Books table with its columns look like as shown in Figure-1

As you see from the above screen, there is no primary key in the table.

Modify the table to add a primary key

We have two options to add the primary key in the existing table in SQL Server.

Method 1 - Add a primary key to the existing table with the following query

ALTER TABLE Books
ADD PRIMARY KEY (Id);

Show the uploaded database table along with the primary key in Id column as highlighted in below Figure-2.

Method 2 - Add primary key to already Existing table without query

In this method, Right click on Books table then select Design as shown in below Figure-3.

Now right Click on 'ID' column and click on set primary key to add primary key to Id column as shown in below Figure-4.

The table with the primary key looks like as shown in below Figure-5.

Create a Table using SQL script with primary key in SQL server

We can set primary key while creating table using SQL Create Table statement as given in the below SQL statement:

Create table Books(
    Id int primary key identity(1,1), 
    Title varchar(50) not null,
    Author varchar(50),
    Description varchar(200),
    Publisher varchar(50),
    Price DECIMAL(10,2),
    CreatedDate datetime, 
    YearPublished datetime,
    LastUpdatedDate datetime
)

Above SQL statement create a table with a primary key column (Id).

Conclusion

Adding a primary key to an existing table is very easy. All you need to do is use the above SQL script and execute it against your database server. You can also use other methods such as T-SQL scripts and visual studio tools, but the easiest way is by using these instructions. In this article, we saw how we can use ALTER TABLE SQL statement to alter a table and add a primary key to an existing table.

1 Comment

Join the conversation! Your thoughts help the community grow.

Bobby Brown2y ago
Thanks for the helpful tips! For those interested in this topic, I suggest exploring this article (https://blog.devart.com/sql-server-primary-key.html), which also describes adding a primary key to an existing table in SQL.