THE WORLD'S LARGEST WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL PRIMARY KEY Constraint



SQL PRIMARY KEY constraint

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

The primary key must contain a unique value.

Primary key columns cannot contain NULL values.

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


SQL PRIMARY KEY constraint when CREATE TABLE

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

MySQL:

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:

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

To name a PRIMARY KEY constraint and define a PRIMARY KEY constraint for multiple columns, use the following SQL syntax:

MySQL /SQL Server /Oracle /MS Access:

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)
)

Note: In the example above, there is only one primary key, PRIMARY KEY (pk_PersonID). However, the value of pk_PersonID is determined by two columns (P_Id and LastName).


SQL PRIMARY KEY constraint when ALTER TABLE

When the table has been created, to create a PRIMARY KEY constraint in the "P_Id" column, use the following SQL:

MySQL /SQL Server /Oracle /MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

To name a PRIMARY KEY constraint and define a PRIMARY KEY constraint for multiple columns, use the following SQL syntax:

MySQL /SQL Server /Oracle /MS Access:

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

Note: If you use the ALTER TABLE statement to add a primary key, you must declare the primary key column to contain no NULL values (when the table is first created).


Revoke PRIMARY KEY constraint

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

MySQL:

ALTER TABLE Persons
DROP PRIMARY KEY

SQL Server /Oracle /MS Access:

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID