Improve the quality of your database using Foreign Keys in PostgreSQL

Introduction

A foreign key is a constraint that specifies that the value of a column (or a group of columns) must match the values of a column in another table. In plain English, if we have two tables, "customer" and "orders", we want foreign keys to make sure orders are entered by valid customers.

Try it yourself

If you would like to try the following examples, please create these 2 tables:

-- Create table -customers-
CREATE TEMP TABLE customers (
   id serial unique,
   name text
);

-- Add a few customers
INSERT INTO customers (name)
VALUES ('Tom'),
       ('Gabriel'),
       ('Bill'),
       ('Bob'),
       ('Jerome');

-- Create table -orders-
CREATE TEMP TABLE orders (
   id serial unique,
   customer_id int REFERENCES customers(id),
   order_description text,
   order_amount double precision
);

Understanding how to create Foreign Keys

Let's have a quick look at the tables we created:

gab@test # \d customers
                        Table "pg_temp_2.customers"
 Column |  Type   |                       Modifiers                        
--------+---------+--------------------------------------------------------
 id     | integer | not null default nextval('customers_id_seq'::regclass)
 name   | text    | 
Indexes:
    "customers_id_key" UNIQUE CONSTRAINT, btree (id)
Referenced by:
    TABLE "orders" CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)

gab@test # \d orders
                                  Table "pg_temp_2.orders"
      Column       |       Type       |                      Modifiers                      
-------------------+------------------+-----------------------------------------------------
 id                | integer          | not null default nextval('orders_id_seq'::regclass)
 customer_id       | integer          | 
 order_description | text             | 
 order_amount      | double precision | 
Indexes:
    "orders_id_key" UNIQUE CONSTRAINT, btree (id)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)

The first table, "customers" has an "id" per entry. You have to specify that this "id" is unique in order to use it with a Foreign Key. You can see at the bottom of its structure it says that its "id" is used as a foreign key constraint in the table "orders". The table "orders" includes a column called "customer_id" which is going to be our reference to "customers". At the bottom of the structure you can also see we are using this column as a foreign key for the column "id" in the table "customers".

Preventing invalid entries

There are currently 5 customers in the table "customers":

gab@test # SELECT * FROM customers;
 id |  name   
----+---------
  1 | Tom
  2 | Gabriel
  3 | Bill
  4 | Bob
  5 | Jerome
(5 rows)

The main purpose of Foreign Keys is to ensure that when we create a new order we use a "customer_id" existing in the table "customers". For example, if I try to create an order for "Gabriel" (id = 2), everything will work fine:

gab@perso # INSERT INTO orders (customer_id, order_description, order_amount) VALUES (2, '1 pound of Coffee', 14.55);
INSERT 0 1
Time: 3.400 ms

However if I try to create an order for a nonexisting customer, PostgreSQL will notify me of my error:

gab@perso # INSERT INTO orders (customer_id, order_description, order_amount) VALUES (12, '2 pound of Coffee', 28.10);
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(12) is not present in table "customers".
Time: 0.688 ms

Restricting deletions and updates (RESTRICT constraint)

You can use a foreign key to prevent deletion and updates of a row referenced in another table. If we follow our example above, we could delete the customer "Tom" because he has no orders but we may want to prevent the deletion of "Gabriel" because he has made an order. Let's modify the structure of column "customer_id" on table "orders" to prevent updates and deletions:

gab@test # ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;
ALTER TABLE

gab@test # ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE

Please note that you can only use "ON UPDATE RESTRICT" or "ON DELETE RESTRICT" and not necessarily both together. Here is a simple example of the RESTRICT constraint:

-- I can delete the customer "Tom" because he has no order:
DELETE FROM customers WHERE id = 1;
DELETE 1

-- I cannot delete the customer "Gabriel" because he has an order:
DELETE FROM customers WHERE id = 2;
ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(2) is still referenced from table "orders".

Applying effects to referenced tables (CASCADE constraint)

With a CASCADE constraint you can apply an UPDATE or a DELETE to the referenced tables automatically. For example, if I were to delete the customer "Gabriel" from the table "customers" it would delete its orders as well. Let's modify the structure of column "customer_id" on table "orders" again:

gab@test # ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;
ALTER TABLE

gab@test # ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE

Just like the RESTRICT constraint, you can only use "ON UPDATE CASCADE" or "ON DELETE CASCADE" and not necessarily both together.

If we now update Gabriel's ID in "customers", it would be updated as well in "orders":

gab@test # UPDATE customers SET id = 20 WHERE name = 'Gabriel';
UPDATE 1
Time: 0.844 ms
[perso] gab@test # SELECT * FROM orders;
 id | customer_id | order_description | order_amount 
----+-------------+-------------------+--------------
  1 |          20 | 1 pound of Coffee |        14.55
(1 row)

And if we delete "gabriel" from the table "customers" he will also be deleted from the table "orders":

gab@test # DELETE FROM customers WHERE name = 'Gabriel';
DELETE 1
Time: 0.618 ms
[perso] gab@gab # SELECT * FROM orders;
 id | customer_id | order_description | order_amount 
----+-------------+-------------------+--------------
(0 rows)

Extend your knowledge

There are a few more options regarding Foreign Keys including default behavior with constraints. I invite you to read the basic Foreign Keys tutorials as well as the in depth documentation about foreign key constraints.