PostgreSQL Foreign Key

Summary: in this tutorial, you will learn about PostgreSQL foreign key and how to add foreign keys to tables using foreign key constraints.

A foreign key is a field or group of fields in a table that uniquely identifies a row in another table. In other words, a foreign key is defined in a table that refers to the primary key of the other table.

The table that contains the foreign key is called referencing table or child table. And the table to which the foreign key references is called referenced table or parent table.

A table can have multiple foreign keys depending on its relationships with other tables.

In PostgreSQL, you define a foreign key through a foreign key constraint. A foreign key constraint indicates that values in a column or a group of columns in the child table match with the values in a column or a group of columns of the parent table. We say that a foreign key constraint maintains referential integrity between child and parent tables.

Define simple PostgreSQL foreign key constraint

Let’s say we have a table named so_headers that stores sales order headers information such as sales order id, customer id, and ship to address:

The line items of a sales orders are stored in another table sales order line items ( so_items):

The primary key of the sales order line items table consists of two columns: item id ( item_id). and sales order id ( so_id).

We assume that the sales order line items table ( so_items) contains data of sales orders that exist. In order to do this, we define a foreign key constraint in the so_items table that references to the so_headers table in the CREATE TABLE statement as follows:

Notice that we use REFERENCES clause to define a foreign key constraint for the so_items table. It means that so_id  column in the so_items table references to the idcolumn of the so_headers table.

Another way to define a foreign key constraint is to use the table constraint as follows:

Because we didn’t specify a name for the foreign key constraint explicitly, PostgreSQL assigned a name with the pattern: table_column_fkey. In our example, PostgreSQL creates a foreign key constraint as so_items_so_id_fkey.

Each line item of a sales order must belong to a specific sales order. Each sales order can have one or many line items. This is call one-to-many relationship. We cannot insert a row into the so_items without referencing to a valid so_id in the so_items table.

What will happen to the rows in the so_items table when a row in the so_headers is deleted? PostgreSQL gives us the following main options: DELETE RESTRICT, DELETE CASCADE and NO ACTION.

PostgreSQL does not delete a row in the so_headers table until all referenced rows in the so_items deleted. To achieve this, we use ON DELETE RESTRICT expression when we define a the foreign key constraint.

PostgreSQL will delete all rows in the so_items table that are referenced to the rows that are being deleted in the so_headers table. To instruct PostgreSQL to do this, we use ON DELETE CASCADE as follows:

If we don’t specify RESTRICT or DELETE action, PostgreSQL will use NO ACTION by default. With NO ACTION, PostgreSQL will raise an error if the referencing rows still exist when the constraint is checked.

Notice that actions for deleting is also applied for updating. It means you can have ON UPDATE RESTRICT, ON UPDATE CASCADE and ON UPDATE NO ACTION.

Define a group of columns as a foreign key

In case a foreign key is a group of columns, we define the foreign key constraint using the following syntax:

Add foreign key constraint to existing table

To add a foreign key constraint to existing table, you use the ALTER TABLE statement as follows:

One final note when you want to add a foreign key constraint with ON DELETE CASCADE to existing table, you need to do the following steps:

1. Drop existing foreign key constraint.
2. Add a new foreign key constraint with  ON DELETE CASCADE action.

In this tutorial, we have introduced you to PostgreSQL foreign key and how to create and change foreign key constraint using CREATE TABLE and ALTER TABLE statements.