Skip to content

Foreign Keys

Definition

Foreign keys are a type of constraint that specifies that the values in a column must match the values appearing in some row of another table.

Why is it useful?

The foreign key constraint ensures data integrity when defining relationships between tables.

Beyond that, you can define behavior for when a referenced data entry is updated or deleted.

Usage

One-to-many relationship

graph LR
parent--1..n-->child
CREATE TABLE parent (
    id SERIAL PRIMARY KEY,
)

CREATE TABLE child (
    id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES parent (id) 
)

Many-to-many relationship

graph LR
foo--n..n-->bar
CREATE TABLE foo(
    id SERIAL PRIMARY KEY
)

CREATE TABLE bar(
    id SERIAL PRIMARY KEY
)

CREATE TABLE foo_bar(
    foo_id INT REFERENCES foo (id)
    bar_id INT REFERENCES bar (id)
    PRIMARY KEY (foo_id, bar_id)
)

ON DELETE and ON UPDATE

CREATE TABLE foo_bar(
    foo_id INT REFERENCES foo (id) ON DELETE CASCADE
    bar_id INT REFERENCES bar (id) ON DELETE CASCADE
    PRIMARY KEY (foo_id, bar_id)
)

Further reading