SQL Primary Key

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

What is the primary key in SQL?

A table consists of columns and rows. Typically, a table has a column or set of columns whose values uniquely identify each row in the table. This column or the set of columns is called the primary key.

The primary key that consists of two or more columns is also known as the composite primary key.

Each table has one and only one primary key. The primary key does not accept NULL or duplicate values.

See the following courses table:

Because the values in the course_id column uniquely identify the rows in the courses table, the course_id column is the primary key of the courses table.

If the primary key consists of two or more columns, the values may be duplicated in one column, but the combination of values from all columns in the primary key must be unique.

See the following training table.

The primary key of the training table consists of the employee_id and course_id columns.

The values in the course_id column are duplicated. However, the combination of values in the employee_id and course_id columns is not.

Creating table with primary key

Generally, you define the primary key when creating the table. If the primary key consists of one column, you can use the PRIMARY KEY constraint as a column or table constraint. If the primary key consists of two or more columns, you must use the PRIMARY KEY constraint as the table constraint.

Suppose you want to manage the projects and project assignments of the company in the database. Therefore, you need to create two tables: projects and project_assignments.

The following statement creates the projects table:

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

You add the PRIMARY KEY in the column definition to make the project_id column as the primary key of the projects table.

The following statement is equivalent to the above statement but instead of using the PRIMARY KEY constraint as the column constraint, it uses the table constraint:

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

You use the CONSTRAINT clause at the end of the CREATE TABLE statement to promote the project_id column to the primary key.

To store the project assignments which represents who was assigned to which project, you need to create the project_assignments table using the following statement:

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

Because the primary key consists of two columns: project_id and employee_id, you must use the PRIMARY KEY as the table constraint.

Adding the primary key with ALTER TABLE statement

First, you can define a table without a primary key using the CREATE TABLE statement though it is not a good practice. Then, you add the primary key to the table using the ALTER TABLE statement.

For example, the following statement creates the project_milestones table without the primary key. The project_milesones stores milestones of projects.

CREATE TABLE project_milestones(
    milestone_id INT,
    project_id INT,
    milestone_name VARCHAR(100)
);Code language: SQL (Structured Query Language) (sql)

Now, you can use the following ALTER TABLE statement to promote the milestone_id column as the primary key.

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

You can skip the CONSTRAINT clause as follows:

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

Removing the primary key constraint

You will rarely remove the primary key of a table. However, in case you must do it, you can use the ALTER TABLE statement as follows:

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

If you are using MySQL, the syntax for removing the primary key is simpler as follows:

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

For example, to remove the primary key constraint of the project_milestones table, you use the following statement.

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

In this tutorial, you have learned about the primary key concept and how to manage a table’s primary key, including adding and removing the primary key.