Primary Key Constraint in SQL

The PRIMARY KEY constraint is used to uniquely identify each row in a table. A PRIMARY KEY can’t have any null value and must have unique values. There can only be one primary key in a table. We can use multiple columns or fields to define a primary key, such a primary key is known as a composite key.

Syntax of PRIMARY KEY Constraint on one column with CREATE TABLE statement:

MySQL:

CREATE TABLE Persons
(
P_Id int NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25),
Address varchar(255),
PRIMARY KEY (P_Id)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
FirstName varchar(25) NOT NULL,
LastName varchar(25),
Address varchar(255),
)

Syntax of PRIMARY KEY Constraint on one column with ALTER TABLE statement:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE CUSTOMER ADD PRIMARY KEY(ID);

Syntax of PRIMARY KEY Constraint on multiple columns with CREATE TABLE statement:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25),
Address varchar(255),
PRIMARY KEY (P_Id, FirstName)
)

Syntax of PRIMARY KEY Constraint on multiple columns with ALTER TABLE statement:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons ADD CONSTRAINT PK_PersonID 
PRIMARY KEY(P_Id, FirstName);

Delete PRIMARY KEY:

Use the following syntax to delete the primary key.

MySQL:

ALTER TABLE Persons DROP PRIMARY KEY

SQL Server / Oracle / MS Access:

ALTER TABLE Persons DROP CONSTRAINT pk_PersonID