PostgreSQL: Primary Keys

In this PostgreSQL post explains how to create, drop, disable, and enable a primary key in PostgreSQL with syntax and examples.

What is a primary key in PostgreSQL?

In PostgreSQL, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a NULL value. A table can have only one primary key.

Note

• In PostgreSQL, a primary key is created using either a CREATE TABLE statement or an ALTER TABLE statement.
• You use the ALTER TABLE statement in PostgreSQL to add or drop a primary key.

Create Primary Key - Using CREATE TABLE statement

You can create a primary key in PostgreSQL with the CREATE TABLE statement.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in PostgreSQL is:

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

  CONSTRAINT constraint_name
   PRIMARY KEY (index_col1, index_col2, ... index_col_n)
);

OR

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

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table. See the PostgreSQL CREATE TABLE statement for more detailed CREATE TABLE syntax as this is an over-simplification to demonstrate how to create a Primary Key.

constraint_name

The name of the primary key.

index_col1, index_col2, ... index_col_n

The columns that make up the primary key.

Example

Let's look at an example of how to create a primary key using the CREATE TABLE statement in PostgreSQL.

CREATE TABLE order_details
( order_detail_id integer NOT NULL,
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200),
  CONSTRAINT order_details_pk PRIMARY KEY (order_detail_id)
);

Or you could also create the primary key on the order_details table using the following syntax:

CREATE TABLE order_details
( order_detail_id integer CONSTRAINT order_details_pk PRIMARY KEY,
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200)
);

In these two examples, we've created a primary key on the order_details table called order_details_pk. It consists of only one column - the order_detail_id column.

We could also create a primary key with more than one field as in the example below:

CREATE TABLE order_details
( order_date date NOT NULL,
  customer_id integer NOT NULL,
  quantity integer,
  notes varchar(200),
  CONSTRAINT order_details_pk PRIMARY KEY (order_date, customer_id)
);

This example creates a primary key called order_details_pk that is made up of a combination of the order_date and customer_id columns. So each combination of order_date and customer_id must be unique in the order_details table.

Create Primary Key - Using ALTER TABLE statement

You can create a primary key in PostgreSQL with the ALTER TABLE statement.

Syntax

The syntax to create a primary key using the ALTER TABLE statement in PostgreSQL is:

ALTER TABLE table_name
  ADD CONSTRAINT [ constraint_name ]
    PRIMARY KEY (index_col1, index_col2, ... index_col_n)

table_name

The name of the table to modify.

constraint_name

The name of the primary key.

index_col1, index_col2, ... index_col_n

The columns that make up the primary key.

Example

Let's look at an example of how to create a primary key using the ALTER TABLE statement in PostgreSQL.

ALTER TABLE order_details
  ADD CONSTRAINT order_details_pk 
    PRIMARY KEY (order_detail_id);

In this example, we've created a primary key on the existing order_details table called order_details_pk. It consists of the order_detail_id column.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE order_details
  ADD CONSTRAINT order_details_pk
    PRIMARY KEY (order_date, customer_id);

This example we've created a primary key called contacts_pk that is made up of a combination of the last_name and first_name columns.

Drop Primary Key

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

Syntax

The syntax to drop a primary key in PostgreSQL is:

ALTER TABLE table_name
  DROP CONSTRAINT constraint_name;

table_name

The name of the table to modify.

constraint_name

The name of the primary key that you wish to drop.

Example

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in PostgreSQL.

ALTER TABLE order_details
  DROP CONSTRAINT order_details_pk;

In this example, we've dropped the primary key on the contacts table. We do not need to specify the name of the primary key as there can only be one on a table.