Using the SQL PRIMARY KEY Constraint

PRIMARY KEY: SQL Constraint Introduced

SQL PRIMARY KEY is a constraint that identifies each table row uniquely. It can be a single column or a group of them. Each column has to have the NOT NULL column constraint applied. One table can only have one primary key.

Defining the Primary Key in SQL

It is recommended to assign the primary key at the time of creating a new table in your database. You will need to use the SQL CREATE TABLE statement with the PRIMARY KEY constraint.

As the syntax requirements differ slightly in different database management systems, you will see two examples of using SQL CREATE TABLE with PRIMARY KEY below. In both of them, we create a new table called Guests and assign the ID column as the primary key.

In MySQL

CREATE TABLE Guests (
    ID int NOT NULL,
    Surame varchar(100) NOT NULL,
    Name varchar(100),
    Age int,
    PRIMARY KEY (ID)
);

In SQL Server / Oracle / MS Access

CREATE TABLE Guests (
    ID int NOT NULL PRIMARY KEY,
    Surname varchar(100) NOT NULL,
    Name varchar(100),
    Age int
);

SQL Composite Primary Key

An SQL composite primary key is one for which multiple columns are used. It's recommended to use the smallest possible number of columns so you don't take up too much disk space.

In the example below, a single key called PK_Guest is defined. However, it consists of two columns – ID and Surname:

CREATE TABLE Guests (
    ID int NOT NULL,
    Surname varchar(100) NOT NULL,
    Name varchar(100),
    Age int,
    CONSTRAINT PK_Guest PRIMARY KEY (ID,Surname)
);

Adding the Primary Key to an Existing Table

You can also assign an SQL primary key to an existing table by using the ALTER TABLE statement:

ALTER TABLE Guests
ADD PRIMARY KEY (ID);

In the example below, we add an SQL composite primary key that consists of two columns:

ALTER TABLE Guests
ADD CONSTRAINT PK_Guest PRIMARY KEY (ID,Surname);

Dropping the Primary Key in SQL

To drop the primary key in SQL, you need to use the ALTER TABLE statement as well. Again, the syntax differs slightly across databases:

In MySQL

ALTER TABLE Guests
DROP PRIMARY KEY;

In SQL Server / Oracle / MS Access

ALTER TABLE Guests
DROP CONSTRAINT PK_Guest;