PRIMARY KEY - This restriction allows you to uniquely identify each record in the table.

The primary key must contain unique values.

The primary key can not contain NULL values.

SQL PRIMARY KEY constraints in CREATE TABLE

The following SQL creates a PRIMARY KEY constraint on column "P_Id", when creating a table "Persons":

MySQL:

1
2
3
4
5
6
7
8

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

SQL Server / Oracle / MS Access:

1
2
3
4
5
6
7

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

MySQL / SQL Server / Oracle / MS Access:

1
2
3
4
5
6
7
8

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

SQL PRIMARY KEY Constraints with ALTER TABLE

MySQL / SQL Server / Oracle / MS Access:

1
2

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

MySQL / SQL Server / Oracle / MS Access:

1
2

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

- Note:

Removal of PRIMARY KEY

To remove a PRIMARY KEY constraint, use the following SQL:

MySQL:

1
2

ALTER TABLE Persons
DROP PRIMARY KEY

SQL Server / Oracle / MS Access:

1
2

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID