Usage and examples of SQL PRIMARY KEY keyword

SQL Keywords Reference

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in the table.

A table can have only one primary key, which can be composed of one or more fields.

SQL PRIMARY KEY on CREATE TABLE

When creating the "Persons" table, the following SQL creates a PRIMARY KEY on the "ID" column:

MySQL:

CREATE　TABLE　Persons
(
　　　ID　int　NOT　NULL,
　　　LastName　varchar(255)　NOT　NULL,
　　　FirstName　varchar(255),
　　　Age　int,
　　　PRIMARY　KEY　(ID)
);

SQL Server / Oracle / MS Access:

CREATE　TABLE　Persons
(
　　ID　int　NOT　NULL　PRIMARY　KEY,
　　LastName　varchar(255)　NOT　NULL,
　　FirstName　varchar(255),
　　Age　int
);

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

MySQL / SQL Server / Oracle / MS Access:

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 above example, there is only one primary key (PK_Person). However, the value of the primary key is composed of two columns (ID+LastName）composes.

PRIMARY KEY on SQL ALTER TABLE

To create a PRIMARY KEY constraint on the "ID" column after the table has been created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD　PRIMARY　KEY　(ID);

To name a PRIMARY KEY constraint and 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_Person PRIMARY KEY (ID,LastName);

Note:If you use the ALTER TABLE statement to add a primary key, the primary key column must already be declared to not contain NULL values (at the time of table creation).

Delete PRIMARY KEY Constraint

To delete 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_Person;

SQL Keywords Reference