PostgreSQL: Primary Keys

This PostgreSQL tutorial 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)
);

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.

PostgreSQL Tutorial

Search This Blog

Tuesday, February 15, 2022