Relations

This section documents standard SQL (ISO/IEC 9075-2 (SQL/Foundation)) functionality only. Vendor-specific extensions or behavior of any particular database management system are intentionally left out. In the US, the standard is also distributed via ANSI’s webstore (search "ISO/IEC 9075"). This content was generated with the assistance of AI and should be verified against your target DBMS’s own documentation before relying on it in production.

Relational databases model associations between tables using foreign keys and, for many-to-many associations, an auxiliary (junction) table. This page covers the four standard relation cardinalities — one-to-one, one-to-many, many-to-one, and many-to-many — with a diagram, CREATE TABLE DDL, and an example join query for each, extending the same sales.orders / sales.customers / sales.order_items example schema used in Data Definition Language (DDL). The join syntax used in each example is documented in full in Querying Data (SELECT). For the authoritative (if less readable) specification, see ISO/IEC 9075-2 (SQL/Foundation), the part of the standard that defines the join syntax and relation modeling covered here.

One-to-one (1:1)

In a one-to-one relation, each row in one table corresponds to at most one row in another. This is modeled by placing a UNIQUE foreign key in one of the two tables, rather than a plain, non-unique foreign key, which would instead allow many rows on that side to reference the same row (a many-to-one relation).

erDiagram ORDERS ||--o| ORDER_INVOICES : has

An order has at most one invoice, enforced by a UNIQUE constraint on the foreign key column:

CREATE TABLE sales.order_invoices (
    invoice_id    INTEGER NOT NULL,
    order_id      INTEGER NOT NULL,
    issued_date   DATE NOT NULL DEFAULT CURRENT_DATE,
    amount        NUMERIC(12, 2) NOT NULL,
    CONSTRAINT pk_order_invoices PRIMARY KEY (invoice_id),
    CONSTRAINT uq_order_invoices_order UNIQUE (order_id),
    CONSTRAINT fk_order_invoices_order FOREIGN KEY (order_id)
        REFERENCES sales.orders (order_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Joining an order to its invoice, if any, uses a LEFT OUTER JOIN so orders without an invoice are still returned (see database/sql/dml-queries.adoc#_left_outer_join for LEFT OUTER JOIN syntax):

SELECT o.order_id, o.order_date, i.invoice_id, i.issued_date, i.amount
FROM sales.orders AS o
LEFT OUTER JOIN sales.order_invoices AS i
  ON o.order_id = i.order_id;

One-to-many (1:N) and many-to-one (N:1)

One-to-many and many-to-one are the same physical relationship viewed from each side: a plain (non-unique) foreign key column on the "many" side references the "one" side’s primary key. Every row on the many side references exactly one row on the one side, but a row on the one side may be referenced by any number of rows on the many side. This is exactly the relationship already shown in Data Definition Language (DDL) between sales.order_items.order_id → sales.orders.order_id (one order has many order items) and sales.orders.customer_id → sales.customers.customer_id (one customer has many orders).

erDiagram ORDERS ||--o{ ORDER_ITEMS : contains

The foreign key is already defined in database/sql/ddl.adoc#_foreign_key:

CREATE TABLE sales.order_items (
    order_id     INTEGER NOT NULL,
    line_number  INTEGER NOT NULL,
    product_id   INTEGER NOT NULL,
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, line_number),
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)
        REFERENCES sales.orders (order_id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION
);

Joining an order to its line items (see database/sql/dml-queries.adoc#_inner_join for INNER JOIN syntax):

SELECT o.order_id, o.order_date, oi.line_number, oi.product_id, oi.quantity
FROM sales.orders AS o
INNER JOIN sales.order_items AS oi
  ON o.order_id = oi.order_id;

Many-to-many (N:N)

A direct foreign key cannot express a many-to-many relation, since a single column can only reference one row on the other side. Instead, many-to-many requires an auxiliary (junction) table holding one foreign key per side, typically with a composite primary key across both foreign keys, so each pairing is stored at most once.

erDiagram PRODUCTS ||--o{ PRODUCT_CATEGORIES : has CATEGORIES ||--o{ PRODUCT_CATEGORIES : has
CREATE TABLE sales.products (
    product_id    INTEGER NOT NULL,
    product_name  CHARACTER VARYING(120) NOT NULL,
    CONSTRAINT pk_products PRIMARY KEY (product_id)
);

CREATE TABLE sales.categories (
    category_id    INTEGER NOT NULL,
    category_name  CHARACTER VARYING(80) NOT NULL,
    CONSTRAINT pk_categories PRIMARY KEY (category_id)
);

CREATE TABLE sales.product_categories (
    product_id   INTEGER NOT NULL,
    category_id  INTEGER NOT NULL,
    CONSTRAINT pk_product_categories PRIMARY KEY (product_id, category_id),
    CONSTRAINT fk_product_categories_product FOREIGN KEY (product_id)
        REFERENCES sales.products (product_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_product_categories_category FOREIGN KEY (category_id)
        REFERENCES sales.categories (category_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Listing each product’s categories requires joining through the junction table with two `INNER JOIN`s (see database/sql/dml-queries.adoc#_inner_join):

SELECT p.product_id, p.product_name, c.category_name
FROM sales.products AS p
INNER JOIN sales.product_categories AS pc
  ON p.product_id = pc.product_id
INNER JOIN sales.categories AS c
  ON pc.category_id = c.category_id;

Summary

Relation type How it’s modeled Example

One-to-one (1:1)

A UNIQUE foreign key on one of the two tables.

sales.order_invoices.order_id → sales.orders.order_id

One-to-many (1:N) / many-to-one (N:1)

A plain (non-unique) foreign key on the "many" side.

sales.order_items.order_id → sales.orders.order_id

Many-to-many (N:N)

An auxiliary/junction table with a foreign key per side, usually with a composite primary key.

sales.product_categories (product_id, category_id)