SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint is used to uniquely identifies each record in a table.

A PRIMARY KEY can identify a single field or a set of fields that uniquely defines a record.

When the PRIMARY KEY is composed of multiple fields is called a composite key.

None of the fields that made up the PRIMARY KEY can contain a NULL value.

A table can have only one primary key.

If you defined a PRIMARY KEY on any field of a table, you could not have two records with the same value of the primary key.

You can either specify a primary key during the creating of a table using the CREATE TABLE statement or after the creation using the ALTER TABLE statement.

SQL PRIMARY KEY on CREATE TABLE

A primary key can be specified at the same time when you create a table.

Syntax

The syntax to create a primary key using the CREATE TABLE statement is as follows:

CREATE TABLE table_name(
    column1 data_type [ NULL | NOT NULL ],
    column1 data_type [ NULL | NOT NULL ],
    ...

    CONSTRAINT constraint_name PRIMARY KEY (pk_column1, pk_column2, ... pk_columnN)
);

Or

CREATE TABLE table_name(
    column1 data_type CONSTRAINT constraint_name PRIMARY KEY,
    column1 data_type [ NULL | NOT NULL ],
    ...
);

Parameters:

• table_name: The name of the table that you want to create.

• colunm1, column2: The columns you want to create in the table.

• constraint_name: The name that you want to give to the primary key.

• pk_column1, pk_column2, ... pk_columnN: The columns that made up the primary key.

Example

Let us see an example of how to create a primary key using the CREATE TABLE statement.

We will start with the case where the primary key consists of just one filed.

The following SQL statement will create a PRIMARY KEY on the "Employee" table called "employee_pk". It consists only of one column, the "id" column.

CREATE TABLE employee (
    id INT NOT NULL,
    first_name VARCHAR(40),
    last_name VARCHAR(40),
    birth_date DATE,
    hire_date DATE,
    
    CONSTRAINT employee_pk PRIMARY KEY (id)
);

You can also use the alternate syntax and create the same primary key as follows:

CREATE TABLE employee (
    id INT CONSTRAINT employee_pk PRIMARY KEY,
    first_name VARCHAR(40),
    last_name VARCHAR(40),
    birth_date DATE,
    hire_date DATE
);

If you want to create a primary key made up of more than one column, you will be limited to using only the first syntax where the primary key is defined at the end of the CREATE TABLE statement.

The following SQL statement will create a primary key called "person_pk" made up of a set of the "first_name" and the "last_name", which means each combination of those fields must be unique in the "Person" table.

CREATE TABLE person (
    first_name VARCHAR(40) NOT NULL,
    last_name VARCHAR(40) NOT NULL,
    birth_date DATE,

    CONSTRAINT person_pk PRIMARY KEY (first_name, last_name)
);

SQL PRIMARY KEY on ALTER TABLE

If you want to add a primary key to an existing table, you can use the ALTER TABLE to create a primary key.

Syntax

The syntax to create a primary key using the ALTER TABLE statement is as follows:

ALTER TABLE table_name 
    ADD CONSTRAINT constraint_name PRIMARY KEY (column1, colmun2, ... columnN);

Parameters:

• table_name: The name of the table that you want to add a primary key to.

• constraint_name: The name that you want to give to the primary key.

• colunm1, column2, ... columnN: The columns that made up the primary key.

Example

Let us see an example of how to create a primary key using the ALTER TABLE statement.

Suppose that we have the "Employee" table already created in our database, and we want to add a primary key to the "Employee" table using the ALTER TABLE statement.

The following SQL statement will create a primary key on the existing "Employee" table called employee_pk. It consists of the "id" column.

ALTER TABLE employee
    ADD CONSTRAINT employee_pk PRIMARY KEY(id);

You can also create a primary key made up of more than one column.

The following SQL statement will create a primary key on the "Person" table called "person_pk". It will be made up of a combination of the "first_name" and the "last_name" columns.

ALTER TABLE person 
    ADD CONSTRAINT person_pk PRIMARY KEY(first_name, last_name);

DROP a PRIMARY KEY Constraint

You can drop a primary key using the ALTER TABLE statement.

The following SQL statement will drop the PRIMARY KEY constraint from the "Employee" table.

• For MySQL, MariaDB:

  ALTER TABLE employee
    DROP PRIMARY KEY;
  

• For Oracle, SQL Server:

  ALTER TABLE employee
    DROP CONSTRAINT employee_pk;