Design: Foreign key constraints

[pikaju]

Summary

Add database-level foreign key constraint support to Toasty's #[belongs_to]
attribute. FK constraints are on by default for SQL drivers, with RESTRICT
as the default referential action. Users can customize actions or disable
constraints entirely via the foreign_key(...) option.

Motivation

Today, Toasty tracks foreign key relationships at the application level only.
The #[belongs_to] attribute defines navigation between models and drives query
planning, but no FOREIGN KEY ... REFERENCES ... clause is ever emitted in DDL.
This means the database cannot enforce referential integrity — orphaned rows,
dangling references, and constraint violations are only caught if the application
code happens to check.

Adding database-level FK constraints gives users:

• Referential integrity enforced by the database
• Cascading deletes/updates without application-level logic
• Better query plans in some databases (e.g. PostgreSQL can use FK metadata)
• Standard SQL behavior that matches developer expectations

User-Facing API

Default: FK constraint with RESTRICT

When no foreign_key option is specified, a FK constraint is emitted with
ON DELETE RESTRICT ON UPDATE RESTRICT:

#[derive(Model)]
struct Todo {
    #[key]
    #[auto]
    id: Id<Self>,

    #[index]
    user_id: Id<User>,

    #[belongs_to(key = user_id, references = id)]
    user: BelongsTo<User>,

    title: String,
}

Generated SQL (PostgreSQL):

CREATE TABLE todos (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    title TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE RESTRICT
);

Custom referential actions

Users specify on_delete and/or on_update inside foreign_key(...):

#[belongs_to(key = user_id, references = id, foreign_key(on_delete = cascade))]
user: BelongsTo<User>,

Only the specified action is overridden; the other remains RESTRICT:

FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE ON UPDATE RESTRICT

Both can be set:

#[belongs_to(
    key = user_id,
    references = id,
    foreign_key(on_delete = cascade, on_update = set_null),
)]
user: BelongsTo<User>,

FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE ON UPDATE SET NULL

Setting both actions at once

Use on_action to set on_delete and on_update to the same value:

#[belongs_to(key = user_id, references = id, foreign_key(on_action = cascade))]
user: BelongsTo<User>,

This is equivalent to:

#[belongs_to(key = user_id, references = id, foreign_key(on_delete = cascade, on_update = cascade))]
user: BelongsTo<User>,

on_action cannot be combined with on_delete or on_update — it is a
shorthand that replaces both.

Disabling FK constraints

To suppress the database constraint entirely:

#[belongs_to(key = user_id, references = id, foreign_key(disabled))]
user: BelongsTo<User>,

No FOREIGN KEY clause is emitted. The relationship still works for query
navigation, eager loading, and all other application-level features — only the
DDL constraint is suppressed.

Referential actions

The following actions are supported for both on_delete and on_update:

Action	Attribute value	SQL output	Description
Restrict	restrict	RESTRICT	Reject the operation if referenced rows exist
Cascade	cascade	CASCADE	Propagate the operation to referencing rows
Set Null	set_null	SET NULL	Set FK columns to NULL (requires nullable FK)
Set Default	set_default	SET DEFAULT	Set FK columns to their default value
No Action	no_action	NO ACTION	Like restrict but deferred-check in some databases

Composite foreign keys

Composite FK fields are listed with multiple key/references pairs, as today.
The foreign_key option applies to the composite constraint as a whole:

#[derive(Model)]
struct LineItem {
    #[key]
    #[auto]
    id: Id<Self>,

    order_id: Id<Order>,
    order_version: i64,

    #[belongs_to(
        key = order_id, references = id,
        key = order_version, references = version,
        foreign_key(on_delete = cascade),
    )]
    order: BelongsTo<Order>,

    quantity: i64,
}

FOREIGN KEY (order_id, order_version) REFERENCES orders(id, version)
    ON DELETE CASCADE ON UPDATE RESTRICT

Nullable foreign keys (optional relationships)

When the FK column is Option<T>, the relationship is optional and the column
is nullable. set_null is only valid when the FK column is nullable — Toasty
rejects set_null on a non-nullable FK at compile time:

#[derive(Model)]
struct Todo {
    #[key]
    #[auto]
    id: Id<Self>,

    // Optional owner
    user_id: Option<Id<User>>,

    #[belongs_to(key = user_id, references = id, foreign_key(on_delete = set_null))]
    user: BelongsTo<Option<User>>,

    title: String,
}

CREATE TABLE todos (
    id UUID PRIMARY KEY,
    user_id UUID,
    title TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE RESTRICT
);

Compile error if misused:

error: `on_delete = set_null` requires a nullable foreign key column,
       but `user_id` is `Id<User>` (non-nullable)
  --> src/main.rs:10:5

NoSQL behavior (DynamoDB)

DynamoDB does not support foreign key constraints. When the driver is DynamoDB:

• The foreign_key(...) option is silently ignored — no error, no warning
• All relationship features (navigation, eager loading, query planning) work as
  before
• foreign_key(disabled) is accepted but has no additional effect

This means a model definition is portable across SQL and NoSQL drivers without
changes. The FK constraint is a SQL-only enhancement.

Schema and migration impact

Database schema (db::Table)

Add an optional list of FK constraints to the table representation:

pub struct Table {
    pub columns: Vec<Column>,
    pub primary_key: Vec<ColumnId>,
    pub indices: Vec<Index>,
    pub foreign_keys: Vec<ForeignKeyConstraint>,  // new
}

pub struct ForeignKeyConstraint {
    pub columns: Vec<ColumnId>,
    pub references_table: TableId,
    pub references_columns: Vec<ColumnId>,
    pub on_delete: ReferentialAction,
    pub on_update: ReferentialAction,
}

pub enum ReferentialAction {
    Restrict,
    Cascade,
    SetNull,
    SetDefault,
    NoAction,
}

Migrations

The migration diff logic gains three new operations:

• AddForeignKey — emits ALTER TABLE ... ADD FOREIGN KEY ...
• DropForeignKey — emits ALTER TABLE ... DROP CONSTRAINT ...
• AlterForeignKey — drop + re-add (most databases don't support in-place alter)

For push_schema (the current drop-and-recreate flow), FK constraints are
included inline in CREATE TABLE.

SQL serialization

The CREATE TABLE serializer adds FK constraints after column definitions:

CREATE TABLE todos (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    title TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE RESTRICT
);

SQLite note: SQLite requires PRAGMA foreign_keys = ON to enforce FK
constraints. Toasty's SQLite driver should enable this pragma on connection.

Table creation ordering

FK constraints require the referenced table to exist first. The schema push
logic must topologically sort tables by FK dependencies before emitting
CREATE TABLE statements.

Parsing changes (toasty-codegen)

The #[belongs_to] attribute parser is extended to accept foreign_key as an
optional nested meta:

belongs_to(
    key = <ident>,
    references = <ident>,
    [key = <ident>, references = <ident>, ...]
    [foreign_key |
     foreign_key(disabled) |
     foreign_key(on_action = <action>) |
     foreign_key(on_delete = <action> [, on_update = <action>])]
)

Parsing rules:

• foreign_key alone (no parens) — equivalent to default (restrict/restrict),
  accepted for explicitness
• foreign_key(disabled) — suppresses the constraint
• foreign_key(on_action = ...) — sets both delete and update actions
• foreign_key(on_delete = ...) — sets delete action, update defaults to
  restrict
• foreign_key(on_update = ...) — sets update action, delete defaults to
  restrict
• foreign_key(on_action = ...) combined with on_delete/on_update —
  compile error
• foreign_key(disabled) combined with any action — compile error

Validation rules

The following are compile-time errors:

Condition	Error message
set_null on non-nullable FK column	on_delete = set_null requires a nullable foreign key column
set_default on FK column without #[default]	on_delete = set_default requires a default value on the foreign key column
foreign_key(disabled, on_delete = ...)	foreign_key(disabled) cannot be combined with referential actions
on_action combined with on_delete/on_update	on_action cannot be combined with on_delete or on_update
Unknown action value	expected one of: restrict, cascade, set_null, set_default, no_action