SQL PRIMARY KEY constraint

At times, ensuring all rows in a table are unique is necessary. For instance, storing information about conference participants—name, email, date of birth, and city—requires avoiding duplicate registrations. In this scenario, a unique combination of data is needed for each participant. While some individuals may share the same name, their email addresses will certainly differ, making it suitable to use this field as a unique key to prevent duplicates. This unique key is commonly referred to as a primary key.

PRIMARY KEY constraint

The PRIMARY KEY constraint specifies a set of columns with values that can help identify any table record.

This constraint can be specified in creating a table named chefs with the columns chef_id INT, first_name VARCHAR(20), and last_name VARCHAR(20). Assuming that all chefs have individual identifiers, make the chef_id column the primary key:

CREATE TABLE chefs (
    chef_id INT PRIMARY KEY, 
    first_name VARCHAR(20), 
    last_name VARCHAR(20)
);

The PRIMARY KEY constraint means that the chef_id column must contain unique values for each chef. No two chefs can have the same chef_id.

Since the primary key has to identify each table row, it must be unique and cannot be null.

Another important thing is that a table can have one and only one primary key, but it is allowed to include multiple columns in it.

Consider the employees table with the columns department_id, employee_id, and name. Assume that it's possible to have two employees with identical identifiers across different departments, but it is impossible to have several employees with identical id's in a single department. So, there can be tuples (42, 15, 'Ann Brown') and (43, 15, 'Bob Freud') in the table, but it's not possible to add a tuple (42, 15, 'John Smith') to that table since there already is an Ann Brown with an id '42'.

In this case, define a named PRIMARY KEY constraint on multiple columns when creating the employees table:

CREATE TABLE employees (
    department_id INT NOT NULL,
    employee_id INT NOT NULL,
    name varchar(50) NOT NULL,
    CONSTRAINT pk_employee PRIMARY KEY (department_id, employee_id)
);

The syntax from the query above can also be used to create a named PRIMARY KEY constraint on one column.

Add a PRIMARY KEY to an existing table

If a table is already without a primary key, add it using the ALTER TABLE statement.

Assume that there is the countries named that was created as follows:

CREATE TABLE countries (
    country_name VARCHAR(40) NOT NULL UNIQUE, 
    population INT CHECK (population > 0), 
    area REAL NOT NULL 
);

To add an unnamed PRIMARY KEY constraint to the country_name column, use the ALTER TABLE ADD PRIMARY KEY statement:

ALTER TABLE countries
ADD PRIMARY KEY (country_name);

The column country_name is already unique and cannot contain null values, so it is safe to make it a primary key of the table countries.

Be careful when adding this constraint to a non-empty table: it will cause an error if there is a duplicate or null values in the potential primary key.

Use the ALTER TABLE ADD CONSTRAINT statement to add a named PRIMARY KEY constraint to an existing table. Let's define a PRIMARY KEY constraint on multiple columns for the students table. This table has the columns name VARCHAR(60), birth_date DATE, and department VARCHAR(40).

The query below will add pk_student as a primary key. This primary key will have two columns: name and birth_date:

ALTER TABLE students
ADD CONSTRAINT pk_student PRIMARY KEY (name,birth_date);

Drop PRIMARY KEY

One possible action is to remove the primary key of a table. 

To delete the primary key pk_student from the table students, e.g. to drop the PRIMARY KEY, use ALTER TABLE DROP PRIMARY KEY:

ALTER TABLE students
DROP PRIMARY KEY;

Since a table can have only one primary key, there is no need to specify the constraint name.