SQL Primary Key

Summary: in this tutorial, you will learn how to use the SQL PRIMARY KEY constraint to add a primary key to the table.

Introduction to SQL Primary Key Constraint #

A primary key is a column or a set of columns that uniquely identifies each row in the table. A primary key ensures that each row is unique.

A table includes one and only one primary key. If a primary key includes one column, that column is called a primary key column.

If a primary key column includes two or more columns, these are primary key columns. The primary key is also known as a composite key.

Here are the main characteristics of a primary key:

• Uniqueness: Each value in the primary key column(s) must be unique.
• Non-NULL: The primary key column cannot contain NULL.
• Immutable: the values in the primary key column should not change.

To create a primary key, you use the PRIMARY KEY constraint:

column1 datatype PRIMARY KEYCode language: SQL (Structured Query Language) (sql)

For example, the following statement uses the CREATE TABLE statement to create a new table called projects with the project_id column as the primary key column:

CREATE TABLE projects (
  project_id INT PRIMARY KEY,
  project_name VARCHAR(255) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Try it

In this example, we use the PRIMARY KEY keywords in the project_id column’s definition. Typically the PRIMARY KEY constraint also enforces the NOT NULL constraint implicitly except SQLite.

In this example, we define the PRIMARY KEY as a column constraint because we declare it in the primary key column’s definition.

SQL allows you to define a primary key column as a table constraint by specifying it after the column list like this:

CREATE TABLE IF NOT EXISTS projects (
  project_id INT,
  project_name VARCHAR(255) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  PRIMARY KEY (project_id)
);Code language: SQL (Structured Query Language) (sql)

Try it

This syntax is more suitable for defining a primary key with two or more columns.

For example, the following statement creates a table called project_assignments that stores the projects assigned to employees:

CREATE TABLE project_assignments (
  project_id INT,
  employee_id INT,
  assigned_date DATE NOT NULL,
  PRIMARY KEY (project_id, employee_id)
);Code language: SQL (Structured Query Language) (sql)

Try it

Primary Key Constraint Names #

When defining a primary key constraint, you can assign it a name using the CONSTRAINT clause:

CONSTRAINT constraint_name
PRIMARY KEY(column1, column2, ...)Code language: SQL (Structured Query Language) (sql)

If you don’t provide a name for the PRIMARY KEY constraint, the database system will generate a default name for it.

This default name may vary depending on databse systems. The following table shows the default names for the primary key constraints in different database systems:

The following example creates a table called project_milestones to store the project milestones:

CREATE TABLE project_milestones (
  milestone_id INT,
  project_id INT NOT NULL,
  milestone_name VARCHAR(255) NOT NULL,
  CONSTRAINT project_milestones_pk PRIMARY KEY (milestone_id)
);Code language: SQL (Structured Query Language) (sql)

In this example, we explicitly name the primary key constraint as project_milestones_pk.

Removing a primary key from a table #

To drop a primary key from a table, you use the ALTER TABLE statement:

ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the table name from which you want to remove the primary key.
• Second, provide the primary key constraint name.

To find the primary key constraint name, you can search for it using the specific command in your database system.

For example, the following statement drops the primary key from the project_milestones table:

ALTER TABLE project_milestones 
DROP CONSTRAINT project_milestones_pk ;Code language: SQL (Structured Query Language) (sql)

Try it

In this statement, the project_milestones_pk is the constraint name of the primary key of the project_milestones table.

Some database systems do not require a primary constraint name such as MySQL. The syntax for dropping a primary key constraint is as follows:

ALTER TABLE table_name
DROP PRIMARY KEY;Code language: SQL (Structured Query Language) (sql)

Adding the primary key to an existing table #

SQL allows you to create a table without a primary key. However, it is not a good practice.

To add a primary key to an existing table, you use the ALTER TABLE statement:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
PRIMARY KEY (column1, column2, ...);Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table you want to ad a primary key.
• Second, optionally provide a constraint name.
• Third, list one or more primary key columns in the PRIMARY KEY clause.

For example, the following statement adds a primary key constraint to the project_milestones table:

ALTER TABLE project_milestones
ADD PRIMARY KEY (milestone_id);Code language: SQL (Structured Query Language) (sql)

Try it

Summary #

• A primary key is one column or a set of columns uniquely identifying each row in a table.
• Use the PRIMARY KEY constraint to define a primary key for a table.
• Use the ALTER TABLE .... ADD PRIMARY KEYstatement to add a primary key to an existing table.
• Use the ALTER TABLE ... DROP CONSTRAINT statement to remove a primary key from a table.

Databases #

• PostgreSQL Primary Key
• MySQL Primary Key
• MariaDB Primary Key
• Oracle Primary Key
• Db2 Primary Key
• SQLite Primary Key

Quiz #