Add a Primary Key

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:

Add Primary key using Create table statement
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:

Create a database in the system.
Create the table in the SQL database.
View the table structure.
Add the primary key to column in the table.
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:

CREATE DATABASE Database_Name;

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

CREATE DATABASE Vehicles;

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:

CREATE TABLE table_name
(
column_Name_1 data type (size of the column_1),
column_Name_2 data type (size of the column_2),
column_Name_3 data type (size of the column_3),
...
column_Name_N data type (size of the column_1)
);

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:

CREATE TABLE Cars
(
Car_Number INT AUTO_INCREMENT,
Model INT,
Cars_Name VARCHAR (20),
Colour VARCHAR (20),
Price INT NOT NULL,
) ;

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:

DESC Cars
or
DESCRIBE Cars ;

Field	Type	NULL	Key	Default	Extra
Car_Number	INT	NO	-	NULL	auto_increment
Model	INT	Yes	-	NULL	-
Car_Name	Varchar(20)	Yes	-	NULL
Color	Varchar(20)	Yes	-	NULL	-
Price	INT	NO	-	NULL	-

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:

CREATE TABLE table_name
(
Column_Name_1 data type (size of the column_1) NOT NULL PRIMARY KEY,
Column_Name_2 data type (size of the column_2),
.......,
Column_Name_N data type (size of the column_N),
) ;

Example

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

CREATE TABLE Cars
(
Car_Number INT AUTO_INCREMENT,
Model INT NOT NULL PRIMARY KEY,
Cars_Name VARCHAR (20),
Color VARCHAR (20) UNIQUE,
Price INT NOT NULL
) ;

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:

DESC Cars;

Field	Type	NULL	Key	Default	Extra
Car_Number	INT	Yes	-	NULL	auto_increment
Model	INT	No	PRI	NULL	-
Car_Name	Varchar(20)	Yes	-	NULL	-
Color	Varchar(20)	Yes	-	NULL	-
Price	INT	NO	-	NULL	-
Average	INT	NO	-	0	-

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:

ALTER TABLE Table_Name DROP PRIMARY KEY;

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

ALTER TABLE Cars DROP PRIMARY KEY;

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:

ALTER TABLE Table_Name ADD CONSTRAINT Constraint_Name PRIMARY KEY (Column_Name);

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

ALTER TABLE Cars ADD CONSTRAINT clr_prmrykey PRIMARY KEY ( Color);

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

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz

Add a Primary Key

Step 1: Create a Database

Step 2: Create a Table and Insert the data

Step 3: View the Table Structure before Primary key Addition

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

Example

Step 5: View the Table Structure after Primary key Addition

Delete Primary key from the table

Add Primary key to the Existing Table

Comment / Reply From

You May Also Like

Popular Post

Language

Newsletter