PostgreSQL FOREIGN KEY Tutorial and Examples

In relational databases, foreign keys are used to define a constrained relationship between two entities. Foreign keys are useful for ensuring data integrity.

What is a foreign key

Foreign keys are used to refer to other tables relative to the primary key. The foreign key is defined in the child table, which corresponds one or more columns of the child table to the primary key or unique key value of the parent table, and establishes an association relationship between the rows of the child table and the rows of the parent table.

Let’s take a look at two tables country and city from Sakila sample database. Here is their relationship diagram:

Here is some rows from the country table:

SELECT *
FROM country
WHERE country_id = 23;

 country_id | country |     last_update
------------+---------+---------------------
         23 | China   | 2006-02-15 04:44:00
(1 row)

Here is some rows from the city table:

SELECT *
FROM city
WHERE country_id = 23;

city_id |     city      | country_id |     last_update
---------+---------------+------------+---------------------
     46 | Baicheng      |         23 | 2006-02-15 04:45:25
     47 | Baiyin        |         23 | 2006-02-15 04:45:25
     80 | Binzhou       |         23 | 2006-02-15 04:45:25
    109 | Changzhou     |         23 | 2006-02-15 04:45:25
    136 | Datong        |         23 | 2006-02-15 04:45:25
...
(53 rows)

From this we can see that the country table and the city table is a one-to-many relationship. A country can have multiple cities, and a city can only be located in one country.

If a country already has cities, you cannot easily delete countries from the country table, otherwise the corresponding city data will be incomplete. You also can’t set a non-existent country_id for a city, otherwise the city data will be wrong.

Foreign key constraints ensure that the data is complete and correct.

Usually, the table that has the foreign key is called the child table, and the table referenced by the foreign key is called the parent table.

PostgreSQL Foreign Key Syntax

Add foreign key when creating table

To add foreign keys when creating a table, use the following syntax:

CREATE TABLE table_name (
  column_defination_1,
  ...
  [CONSTRAINT foreign_key_name]
    FOREIGN KEY (column)
      REFERENCES parent_table_name (column)
      ON UPDATE ...
      ON DELETE ...
;
);

Explanation:

The foreign_key_name is the name of the foreign key constraint. CONSTRAINT foreign_key_name is optional.
Tge FOREIGN KEY (column) indicates that the column column is a foreign key.
The REFERENCES parent_table_name (column) indicates that the foreign key refers to the column column in the parent_table_name table.
The ON DELETE and ON UPDATE specifies the constraint strategy to take when deleting or updating rows in the parent table. You can use one of the following 5 strategies:
- NO ACTION: This is the default policy.
- RESTRICT: A PostgreSQL error is raised when attempting to delete or update a row in the parent table if a row in the parent table has a matching row in the child table.
- CASCADE: If a row in the parent table is deleted or updated, the value of the matching row in the child table is automatically deleted or updated.
- SET NULL: If a row in the parent table is deleted or updated, the value of the matching row in the child table is set to NULL.
- SET DEFAULT: If a row in the parent table is deleted or updated, the value of the matching row in the child table is set to the default value.

Let’s look at the foreign key constraints defined by the city table:

\d city

                                          Table "public.city"
  Column    |            Type             | Collation | Nullable |                Default
-------------+-----------------------------+-----------+----------+---------------------------------------
city_id     | integer                     |           | not null | nextval('city_city_id_seq'::regclass)
city        | character varying(50)       |           | not null |
country_id  | smallint                    |           | not null |
last_update | timestamp without time zone |           | not null | now()
Indexes:
   "city_pkey" PRIMARY KEY, btree (city_id)
   "idx_fk_country_id" btree (country_id)
Foreign-key constraints:
   "city_country_id_fkey" FOREIGN KEY (country_id) REFERENCES country(country_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
   TABLE "address" CONSTRAINT "address_city_id_fkey" FOREIGN KEY (city_id) REFERENCES city(city_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
   last_updated BEFORE UPDATE ON city FOR EACH ROW EXECUTE FUNCTION last_updated()

Note the part of the foreign key:

Foreign-key constraints:
    "city_country_id_fkey" FOREIGN KEY (country_id) REFERENCES country(country_id) ON UPDATE CASCADE ON DELETE RESTRICT

Add foreign key when altering a table

If the foreign key is not defined when the table is creating, you can also add the foreign key later by using the following syntax:

ALTER TABLE child_table_name
ADD [CONSTRAINT foreign_key_name]
  FOREIGN KEY (column)
    REFERENCES parent_table_name (column)
    ON UPDATE ...
    ON DELETE ...
;

Explanation:

Use the ALTER TABLE statement to modify the definition of the table.
Use the ADD [CONSTRAINT foreign_key_name] to add a foreign_key_name constraint named. It is optional.
The foreign key is defined using FOREIGN KEY (column)) REFERENCES parent_table_name (column).

Drop Foreign Keys Syntax

To drop a foreign key on a table, you can use the following syntax:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Explanation:

Use the ALTER TABLE statement to modify the definition of the table.
The DROP CONSTRAINT specifies the constraint name after it. It can remove any constraint by name, not just foreign keys.

PostgreSQL FOREIGN KEY instance

The following example will create two tables users and user_hobbies in the testdb database, where foreign keys are used in the user_hobbies table to reference the users table. Please follow the steps below:

Log in to the PostgreSQL database as postgres user:
```
[~] psql -U postgres
psql (14.4)
Type "help" for help.
```
Note: You can also log in as any other user with appropriate database privileges.
Connect to the testdb database:
```
\c testdb
```
If you haven’t created the database yet, run the following statement first:
```
CREATE DATABASE testdb;
```

Create the users table:

CREATE TABLE users (
  user_id INTEGER NOT NULL,
  name VARCHAR(45) NOT NULL,
  PRIMARY KEY (user_id)
);

So far, we have created the users table.

`CASCADE` Policy Example

If the ON DELETE and ON UPDATE use the CASCADE strategy:

If a row in the parent table is deleted, matching rows in the child table are also deleted.
If the key value of the row of the parent table is updated, the columns of the matching row in the child table are also updated.

Use the following SQL to create a user_hobbies table with foreign keys using the CASCADE strategy.

DROP TABLE IF EXISTS user_hobbies;
CREATE TABLE user_hobbies (
  hobby_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  hobby VARCHAR(45) NOT NULL,
  PRIMARY KEY (hobby_id),
  FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

The following statement inserts some rows into the two tables:

DELETE FROM users;
DELETE FROM user_hobbies;
INSERT INTO users (user_id, name)
VALUES (1, 'Tim');
INSERT INTO user_hobbies (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

At this point the rows in the user_hobbies table:

hobby_id | user_id |  hobby
----------+---------+----------
       1 |       1 | Football
       2 |       1 | Swimming
(2 rows)

Let’s take a look at the associated operation of the child table caused by the UPDATE and DELETE operation on the parent table:

The UPDATE operate on the parent table

We modify the value of the user_id in the parent table users from 1 to 100:
```
UPDATE users
SET user_id = 100
WHERE user_id = 1;
```
At this point the rows in the user_hobbies table:
```
hobby_id | user_id |  hobby
----------+---------+----------
       1 |     100 | Football
       2 |     100 | Swimming
(2 rows)
```
We found that the value of user_id 1 in the user_hobbies table and users table was automatically modified to 100.
The DELETE operate on the parent table
```
DELETE FROM users
WHERE user_id = 100;
```
At this point the rows in the user_hobbies table:
```
hobby_id | user_id | hobby
----------+---------+-------
(0 rows)
```
We found that those rows with user_id 100 in user_hobbies table and users table were deleted.

`RESTRICT` strategy

If the ON DELETE and ON UPDATE use the RESTRICT strategy:

PostgreSQL prohibits deleting rows in parent tables that match child tables.
PostgreSQL prohibits dropping the value of the key of the row in the parent table that matches the child table.

Use the following SQL to create a user_hobbies table with foreign keys using the RESTRICT strategy.

DROP TABLE IF EXISTS user_hobbies;
CREATE TABLE user_hobbies (
  hobby_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  hobby VARCHAR(45) NOT NULL,
  PRIMARY KEY (hobby_id),
    FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
);

Insert some rows into two tables:

DELETE FROM users;
DELETE FROM user_hobbies;
INSERT INTO users (user_id, name)
VALUES (1, 'Tim');
INSERT INTO user_hobbies (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

At this point the rows in the user_hobbies table:

hobby_id | user_id |  hobby
----------+---------+----------
       1 |       1 | Football
       2 |       1 | Swimming
(2 rows)

Let’s see the result of the UPDATE and DELETE operation on the parent table:

The UPDATE operate on the parent table

UPDATE users
SET user_id = 100
WHERE user_id = 1;

The PostgreSQL server returned the following error:

ERROR:  update or delete on table "users" violates foreign key constraint "user_hobbies_user_id_fkey" on table    "user_hobbies"
DETAIL:  Key (user_id)=(1) is still referenced from table "user_hobbies".

The DELETE operate on the parent table

DELETE FROM users
WHERE user_id = 1;

The PostgreSQL server returned the following error:

ERROR:  update or delete on table "users" violates foreign key constraint "user_hobbies_user_id_fkey" on table "user_hobbies"
DETAIL:  Key (user_id)=(1) is still referenced from table "user_hobbies".

`SET NULL` policy

If the ON DELETE and ON UPDATE use the SET NULL strategy:

If a row of the parent table is deleted, the value of the column of the matching row in the child table is set to NULL.
If the key value of a row in the parent table is updated, the column value of the matching row in the child table is set to NULL.

Use the following SQL to create a user_hobbies table with foreign keys using the SET NULL strategy.

DROP TABLE IF EXISTS user_hobbies;
CREATE TABLE user_hobbies (
  hobby_id INTEGER NOT NULL,
  user_id INTEGER,
  hobby VARCHAR(45) NOT NULL,
  PRIMARY KEY (hobby_id),
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE SET NULL
    ON UPDATE SET NULL
);

Insert some rows into two tables:

DELETE FROM users;
DELETE FROM user_hobbies;
INSERT INTO users (user_id, name)
VALUES (1, 'Tim');
INSERT INTO user_hobbies (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

Let’s take a look at the associated operation of the child table caused by the UPDATE and DELETE operation on the parent table:

The UPDATE operate on the parent table
```
UPDATE users
SET user_id = 100
WHERE user_id = 1;
```
At this point the rows in the user_hobbies table:
```
hobby_id | user_id |  hobby
----------+---------+----------
       1 |  <null> | Football
       2 |  <null> | Swimming
(2 rows)
```
After updating the value of the user_id column in the parent table, the value of the column user_id in those corresponding rows in the user_hobbies table is set to NULL.

The DELETE operate on the parent table

Since the above example modifies the data of the table, we reinitialize the data of the two tables:

DELETE FROM users;
DELETE FROM user_hobbies;
INSERT INTO users (user_id, name)
VALUES (1, 'Tim');
INSERT INTO user_hobbies (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

DELETE FROM users
WHERE user_id = 1;

At this point the rows in the user_hobbies table:

hobby_id | user_id |  hobby
----------+---------+----------
       1 |  <null> | Football
       2 |  <null> | Swimming
(2 rows)

After the rows with user_id 1 was deleted, the value of the column user_id in those corresponding rows in the user_hobbies table is set to NULL.

Self-referencing foreign keys

Sometimes, the child table and the parent table may be the same table. A foreign key in such a table is called a self-referential foreign key.

Typically, self-referential foreign keys are defined in tables that represent tree-like data structures. For example, the following is a table representing categories:

CREATE TABLE category (
  category_id INTEGER PRIMARY KEY,
  category_name VARCHAR(45),
  parent_category_id INTEGER,
  CONSTRAINT fk_category FOREIGN KEY (parent_category_id)
    REFERENCES category (category_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

In this table, the column parent_category_id is a foreign key. It references the category_id column of the category table.

This table implements an infinite hierarchy of classification trees. A category can have multiple subcategories, and a category can have 0 or 1 parent category;

Conclusion

In this article, we introduced what foreign keys are, their rules, and how to use them in PostgreSQL. Here are the main points of this article:

Foreign keys are used to define constraints between two entities. Foreign keys are useful for ensuring data integrity.
The table that defines the foreign key is called the child table, and the table referenced by the foreign key is called the parent table.
The foreign key refers to the primary key or unique key column of the parent table.
The ALTER TABLE ... ADD FOREIGN KEY ... statement can be used to add foreign keys.
The ALTER TABLE ... DROP CONSTRAINT ... statement can be used to delete foreign keys.
A self-referential foreign key refers to the current table itself. This implements tree-like data structures.

What is a foreign key

PostgreSQL Foreign Key Syntax

Add foreign key when creating table

Add foreign key when altering a table

Drop Foreign Keys Syntax

PostgreSQL FOREIGN KEY instance

CASCADE Policy Example

RESTRICT strategy

SET NULL policy

Self-referencing foreign keys

Conclusion

On this page

`CASCADE` Policy Example

`RESTRICT` strategy

`SET NULL` policy