Relationships

A relationship is a foreign key linking a child table to a parent’s primary key. In the example library each book is written by one author, so books (the child) points at authors (the parent):

Declare a 1:n relationship, then draw it with show relationship.

add 1:n relationship as books_author from authors.author_id to books.author_id on delete cascade

You name the relationship with as <name> (here books_author), then give the parent endpoint from authors.author_id and the child endpoint to books.author_id. The 1:n reads “one author, many books”.

Viewing a relationship

show relationship draws the two tables and the link between them:

show relationship books_author

┌───────────────────────┐                  ┌───────────────────────────┐
│ books                 │                  │ authors                   │
├──────────────┬────────┤                  ├──────────────────┬────────┤
│ book_id (PK) │ serial │         ┌──────1▶│ author_id (PK) ● │ serial │
│ title        │ text   │         │        │ name             │ text   │
│ author_id ●  │ int    │n────────┘        │ birth_year       │ int    │
│ published    │ int    │                  └──────────────────┴────────┘
│ isbn         │ text   │
└──────────────┴────────┘
  on delete cascade · on update no action

Read the diagram like this:

• The child (the table holding the foreign key) is on the left; the parent is on the right.
• A filled dot ● marks each column that takes part in the link.
• The connector runs n … 1 — many child rows to one parent row — and the ▶ arrowhead points at the parent key.
• The line beneath records the referential actions (below).

A table’s own show table includes a compact Relationships section that summarises every link it takes part in:

show table books

books
┌───────────┬────────┬─────────────┐
│ Name      │ Type   │ Constraints │
├───────────┼────────┼─────────────┤
│ book_id   │ serial │ PK          │
│ title     │ text   │ NOT NULL    │
│ author_id │ int    │             │
│ published │ int    │             │
│ isbn      │ text   │ UNIQUE      │
└───────────┴────────┴─────────────┘
Relationships
┌─────────────┐                  ┌─────────────┐
│ books       │                  │ authors     │
├─────────────┤                  ├─────────────┤
│ author_id ● │n───────────────1▶│ author_id ● │
└─────────────┘                  └─────────────┘
  on delete cascade · on update no action

For a one-line summary of every relationship in the project, use show relationships (see Querying & inspecting).

Referential actions

on delete and on update decide what happens to the child rows when a parent row is deleted or its key changes. The action is one of:

Action	Effect on the children
cascade	The children are deleted / updated to match.
set null	The children’s foreign-key column is set to empty.
restrict	The change is refused while children still reference the row.

With on delete cascade, deleting an author removes that author’s books too, and the result reports each affected relationship:

delete from authors where author_id = 3

  1 row(s) deleted
  related: 1 row(s) deleted in `books` for relationship `books_author` (on delete cascade)

Creating the child column at the same time

If the child column does not exist yet, add --create-fk and the playground creates it for you with the right type:

add 1:n relationship as loans_member from members.member_id to loans.member_id --create-fk

Compound keys

To reference a parent’s compound primary key, list the columns on each side, matched in order:

add 1:n relationship from <Parent>.(<a>, <b>) to <Child>.(<x>, <y>)

Many-to-many relationships

When two tables relate many-to-many — a student takes many courses, and a course has many students — you model it with a junction table (also called a bridge table) that holds a foreign key to each side. create m:n relationship builds that junction for you in one step:

create m:n relationship from Students to Courses

Assuming each parent has a primary key id, this creates a table named Students_Courses with:

• one foreign-key column per primary-key column of each parent — Students_id and Courses_id, typed to match the keys they reference;
• a compound primary key over those columns, so the same pair can never be linked twice;
• two 1:n relationships (junction → Students, junction → Courses), each with on delete cascade and on update cascade — delete a student or a course and its link rows go with it.

The junction is an ordinary table afterwards: rename table, drop table, insert, or add column all work on it. To choose the name yourself, add as <name>:

create m:n relationship from Students to Courses as Enrollments

A few rules keep it predictable:

• Both parents need a primary key — that is what the junction references.
• The two tables must be different; there is no self-referential shorthand. To link a table to itself, build the junction by hand.
• The generated foreign keys always cascade. If you need different referential actions, or extra columns on the link from the start (a loan date, say), build the junction by hand instead — a create table plus two add 1:n relationship commands, as the library guide does for its loans table.

Removing a relationship

drop relationship books_author

In advanced mode

Relationships are ordinary foreign keys, declared with FOREIGN KEY … REFERENCES either inline at CREATE TABLE or added later with ALTER TABLE:

alter table books add foreign key (author_id) references authors (author_id) on delete cascade

Syntax

add 1:n relationship [as <Name>]
  from <Parent>.<col> to <Child>.<col>
  [on delete <action>] [on update <action>]
  [--create-fk]
create m:n relationship from <T1> to <T2> [as <Name>]
drop relationship <Name>

See also Indexes and Types (why a foreign-key column’s type can differ from the key it references).