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:
(
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:
(
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:
(
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:
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:
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:
DROP PRIMARY KEY
SQL Server / Oracle / MS Access:
DROP CONSTRAINT pk_PersonID