Black friday

Save big!

All courses under $5 - for a limited time!

Code has been added to clipboard!

Using the SQL PRIMARY KEY Constraint

Reading time 2 min
Published Aug 9, 2017
Updated Oct 8, 2019

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

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

In SQL Server / Oracle / MS Access

Example
CREATE TABLE Guests (
    ID int NOT NULL PRIMARY KEY,
    Surname varchar(100NOT NULL,
    Name varchar(100),
    Age int
);
Theory is great, but we recommend digging deeper!

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:

Example
CREATE TABLE Guests (
    ID int NOT NULL,
    Surname varchar(100NOT 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:

Example
ALTER TABLE Guests
ADD PRIMARY KEY (ID);

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

Example
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

Example
ALTER TABLE Guests
DROP PRIMARY KEY;

In SQL Server / Oracle / MS Access

Example
ALTER TABLE Guests
DROP CONSTRAINT PK_Guest;