How to Add a Primary Key in SQL

In this article, we will learn how to add Primary Key to the column in the table of our SQL database.

The PRIMARY KEY is used to retrieve each record of the SQL table. The field defined as the PRIMARY KEY must contain different and NOT NULL values. You can easily add a primary key to the column in the following two ways:

1. Add Primary key using Create table statement
2. Add primary key using Alter Table statement

If you want to add primary key to a column in the table, you have to follow the below steps in the given sequence:

1. Create a database in the system.
2. Create the table in the SQL database.
3. View the table structure.
4. Add the primary key to column in the table.
5. View the table structure.

Now, we are going to explain the above steps with an example.

Step 1: Create a Database

In the structured query language, creating a database is the first step for storing the structured tables in the database.

Use the following SQL syntax to create a database:

Suppose you want to create the Vehicles database. For this, you have to type the following command in Structured Query Language:

Step 2: Create a Table and Insert the data

Now, you have to use the following SQL syntax to create a table in your database:

Suppose you want to create the Cars table in the Vehicles database. For this, you have to type the following query in your SQL application:

Step 3: View the Table Structure before Primary key Addition

After table creation and data insertion, you can view the structure of the Cars table by typing the following query in your SQL application:

Step 4: Add a Primary key to the column in the table

If you want to add the primary key at the time of table creation, then you have to use the following CREATE TABLE syntax in SQL:

Example

The following query creates 'Cars' table and adds the PRIMARY KEY constraint on the 'Model' column:

This query in SQL does not allow database users to insert the same model of car in the Cars table.

Step 5: View the Table Structure after Primary key Addition

To check the result of the query executed in the 4^th step, you have to type the following DESC command in SQL:

You can see in the above SQL output that the Model column is created as the primary key. Now, the Model column must contain unique and NOT NULL values. If you enter the same and NULL values in this column, the SQL system will show an error.

Delete Primary key from the table

If you want to delete the Primary key from the column of the table, then you have to use the following ALTER syntax in SQL:

The following query deletes the PRIMARY KEY from the Model column of the Cars table:

Add Primary key to the Existing Table

If you want to add a primary key in the existing table, you have to use the following ALTER syntax in SQL:

The following query adds a PRIMARY KEY constraint on the Color column when the Cars table already exists in the database system:

This ALTER query in SQL will not allow you to insert the same color of a car in the Cars table.