SQL Primary Key

 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 value uniquely identifies 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.

See the following courses table.

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

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

In case the primary key consists of two or more columns, the values may be duplicate 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 the table consists of the employee_id and course_id columns.

The values in the course_id column are duplicate however the combination of values in the employee_id and course_id are not.

Creating a table with a 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. In case 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
);

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)
);

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)
);

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 the 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)
);

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);

You can skip the CONSTRAINT clause as follows:

ALTER TABLE project_milestones
ADD PRIMARY KEY (milestone_id);

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;

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

ALTER TABLE table_name
DROP PRIMARY KEY;

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;

In this tutorial, we have introduced you to the primary key concept and shown you how to manage the primary key of a table including adding and removing the primary key.