SQL PRIMARY KEY Constraint
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.
A PRIMARY KEY constraint ensures unique values, and cannot contain NULL values
(it is a combination of both a UNIQUE constraint and a
NOT NULL constraint).
A table can have only ONE PRIMARY KEY
constraint. The primary key can either be a single column, or a combination of columns.
Tip: The primary key is the target for
FOREIGN KEY
constraints in other tables (which enforces referential integrity between data in two tables).
PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "ID" column
upon creation of the "Persons" table:
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);MySQL:
CREATE TABLE Persons
(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);PRIMARY KEY on Multiple Columns
To define a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Persons
(
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);Note: In the example above there is one
PRIMARY KEY (PK_Person), and the value is made up of two columns (ID + LastName).
PRIMARY KEY on ALTER TABLE
To create a PRIMARY KEY constraint on the "ID" column when the table already
has been created, use the following SQL:
ALTER TABLE Persons
ADD PRIMARY KEY (ID);PRIMARY KEY on Multiple Columns
To define a
PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);Note: When using ALTER TABLE to add a primary key, the primary key column(s) must
have been declared with NOT NULL upon creation of the table.
Drop a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY;
