SQL PRIMARY KEY Constraint

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

The primary key must contain unique values.

The primary key column cannot contain NULL values.

Each table should have a primary key, and each table can only have one primary key.

SQL PRIMARY KEY Constraint on CREATE TABLE

The following SQL creates a PRIMARY KEY constraint on the "Id_P" column when the "Persons" table is created:

MySQL:

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

SQL Server / Oracle / MS Access:

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

If you need to name the PRIMARY KEY constraint and define PRIMARY KEY constraints for multiple columns, please use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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

SQL PRIMARY KEY Constraint on ALTER TABLE

If you create a PRIMARY KEY constraint for the "Id_P" column when the table already exists, please use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)

If you need to name the PRIMARY KEY constraint and define PRIMARY KEY constraints for multiple columns, please use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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

Note:If you use the ALTER TABLE statement to add a primary key, you must declare the primary key column as not containing NULL values (at the time of the table's first creation).

Revoke PRIMARY KEY Constraint

To revoke the PRIMARY KEY constraint, please use the following SQL:

MySQL:

ALTER TABLE Persons
DROP PRIMARY KEY

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID