SQL PRIMARY KEYConstraints

SQL PRIMARY KEY Constraint

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

PRIMARY KEY must contain unique values and cannot contain NULL values.

Each table can have only one PRIMARY KEY, which can consist of a single column or multiple columns.

SQL PRIMARY KEY Constraint on 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 to define a PRIMARY KEY constraint on 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 (pk_PersonID). However, the value of pk_PersonID is composed of two columns (P_Id and LastName).

SQL PRIMARY KEY Constraint on ALTER TABLE

When the table has already been created, to create a PRIMARY KEY constraint on 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 to define a PRIMARY KEY constraint on 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 as not containing NULL values (when the table was first created).

Drop 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