SQL Query to Create Table With a Primary Key

A primary key uniquely identifies each row table. It must contain unique and non-NULL values. A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called composite keys.

To create a Primary key in the table, we have to use a keyword; “PRIMARY KEY ( )”

Query:

CREATE TABLE `Employee` ( `Emp_ID` VARCHAR(20) NOT NULL ,`Name` VARCHAR(50) NOT NULL ,  
`Age` INT NOT NULL ,`Phone_No` VARCHAR(10) NOT NULL ,`Address` VARCHAR(100) NOT NULL ,
 PRIMARY KEY (`Emp_ID`));

To view whether “Emp_ID” is the primary key or not we use Describe command to view the structure of the Table.

DESCRIBE is used to describe something. Since in database we have tables, that’s why we use DESCRIBE or DESC(both are same) command to describe the structure of a table.

Query:

DESCRIBE Employee;
Or 
DESC Employee;

Output:

Now, to create a PRIMARY KEY constraint on any column when the table already exists (NO EARLIER PRIMARY KEY DEFINED), use the following SQL Syntax:

ALTER TABLE [Table_Name] ADD PRIMARY KEY (ID);

Query:

ALTER TABLE Employee ADD PRIMARY KEY (Phone_No);

Output:

If any earlier primary key is defined, then there will be errors like;

Output:

This error is because; Only one primary key can exist. So, we have to first delete the initial PRIMARY KEY to create a new PRIMARY KEY.

1. To create PRIMARY KEY on multiple columns:

Query:

CREATE TABLE `Employee` ( `Emp_ID` VARCHAR(20) NOT NULL ,
                      `Name` VARCHAR(50) NOT NULL ,  
                      `Age` INT NOT NULL ,  
                      `Phone_No` VARCHAR(10) NOT NULL ,
                      `Address` VARCHAR(100) NOT NULL ,
                      PRIMARY KEY (`Emp_ID`,`Name`));

Output:

2. Add Multiple Primary Keys when Table already existing

Query:

ALTER TABLE Employee
 ADD CONSTRAINT PK_CUSTID PRIMARY KEY (Emp_ID, NAME);
 DESC Employee;

Output: