Latest web development tutorials

SQL PRIMARY KEY constraint

SQL PRIMARY KEY constraint

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

The primary key must contain unique values.

A primary key column can not contain NULL values.

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

SQL PRIMARY KEY constraint CREATE TABLE when

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

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 PRIMARY KEY constraints, and define a plurality of columns PRIMARY KEY constraint, 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, only one primary key PRIMARY KEY (pk_PersonID).However, the value pk_PersonID is made up of two columns (P_Id and LastName) thereof.

SQL PRIMARY KEY constraint ALTER TABLE when

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

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

To name PRIMARY KEY constraints, and define a plurality of columns PRIMARY KEY constraint, 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 does not contain a NULL value (first created in the table).

Revocation of PRIMARY KEY constraint

To drop a 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