SQL PRIMARY KEY

In this tutorial, we will learn how to use PRIMARY KEY using SQL.

SQL PRIMARY KEY Constraint

• SQL PRIMARY KEY Constraint ensures that only unique record is inserted in a table.
• SQL PRIMARY KEY is used to uniquely identify each row in a table.
• SQL PRIMARY KEY cannot contain NULL values, it must contains unique values.
• A table can have only one PRIMARY KEY, which may consist of single or multiple fields.

SQL PRIMARY KEY to CREATE TABLE Statement

To add a PRIMARY KEY constraint when a table is created, a statement  is as follow:

SQL Server / Oracle

CREATE TABLE Employee(
    ID int NOT NULL PRIMARY KEY,
    EmpName varchar(255) NOT NULL,
    City varchar(255),
    Age int,
    Salary decimal(18,2)
);

MySQL

CREATE TABLE Employee(
    ID int NOT NULL,
    EmpName varchar(255) NOT NULL,
    City varchar(255),
    Age int,
    Salary decimal(18,2),
    PRIMARY KEY (ID)
);

SQL PRIMARY KEY on Multiple Column

To define a PRIMARY KEY constraint on multiple columns, a statement  is as follow:

MySQL / SQL Server / Oracle

CREATE TABLE Employee (
    ID int NOT NULL,
    EmpName varchar(255) NOT NULL,
    City varchar(255),
    Age int,
    Salary decimal(18,2),
    CONSTRAINT PK_Employee UNIQUE (ID,EmpName)
);

SQL PRIMARY KEY using ALTER TABLE Statement

MySQL / SQL Server / Oracle

To add a PRIMARY KEY constraint after creating a table, a statement is as follow:

ALTER TABLE Employee
ADD PRIMARY KEY(ID);

SQL PRIMARY KEY using ALTER TABLE Statement on Multiple Columns

To add a PRIMARY KEY constraint after creating a table on multiple columns,  a statement is as follow:

ALTER TABLE Employee
ADD CONSTRAINT PK_Employee UNIQUE (ID,EmpName);

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY Constraint, the statement is as follow:

SQL Server / Oracle

ALTER TABLE tableName
DROP CONSTRAINT PK_constraintName;

ALTER TABLE Employee
DROP CONSTRAINT PK_Employee;

MySQL

ALTER TABLE Employee
DROP PRIMARY KEY;