Foreign Key Management

Relevant source files

The following files were used as context for generating this wiki page:

• src/backend/foreign_key_builder.rs
• src/backend/mysql/foreign_key.rs
• src/backend/postgres/foreign_key.rs
• src/backend/table_builder.rs
• src/lib.rs
• README.md

Foreign Key Management in SeaQuery provides a platform-agnostic API for defining, creating, and dropping relational constraints between database tables. It allows developers to construct complex foreign key relationships using Rust's type system, which are then translated into backend-specific SQL for MySQL, PostgreSQL, or SQLite.

This system handles the syntactic differences between database engines, such as MySQL's use of ADD CONSTRAINT vs. SQLite's requirement that foreign keys be defined during table creation. It supports standard referential actions like CASCADE, RESTRICT, and SET NULL for both delete and update operations.
Sources: README.md:1-20, src/lib.rs:560-590

Architecture and Components

The foreign key system is built upon a trait-based architecture where generic statements are processed by backend-specific builders. The central trait is ForeignKeyBuilder, which defines the interface for translating abstract statements into SQL strings.

Key Data Structures

The system uses several core structures to represent foreign key logic:

• ForeignKeyCreateStatement: Encapsulates the configuration for adding a new foreign key.
• ForeignKeyDropStatement: Represents the intent to remove an existing constraint.
• ForeignKeyAction: An enum representing referential integrity rules (e.g., Cascade, SetNull).
• Mode: An internal enum used to distinguish if a foreign key is being handled during initial table creation or via an ALTER TABLE statement.

Sources: src/backend/foreign_key_builder.rs:5-10, src/lib.rs:595-605

Component Relationship

The following diagram illustrates how the generic foreign key statements interact with the backend builders to produce SQL.

The diagram shows the flow from a generic statement through the specific backend implementations to the final SQL output.
Sources: src/backend/foreign_key_builder.rs:13-30, src/backend/mysql/foreign_key.rs:1-10, src/backend/postgres/foreign_key.rs:1-10

Foreign Key Creation

Foreign keys can be created either as part of a Table::create() statement or as a standalone ForeignKey::create() operation. Standalone creation typically results in an ALTER TABLE statement in supported backends.

Referential Actions

SeaQuery supports the standard SQL foreign key actions through the ForeignKeyAction enum.

Action	Description	SQL Keyword
Restrict	Prevents the action on the parent table if child rows exist.	RESTRICT
Cascade	Automatically deletes/updates child rows when parent is changed.	CASCADE
SetNull	Sets the foreign key column in child rows to NULL.	SET NULL
NoAction	Similar to Restrict, but checked at the end of the transaction.	NO ACTION
SetDefault	Sets the child row column to its default value.	SET DEFAULT

Sources: src/backend/foreign_key_builder.rs:34-45

Implementation Logic

Backends implement prepare_foreign_key_create_statement_internal to handle the specific syntax. For example, MySQL and Postgres both use the CONSTRAINT keyword, but they differ in how they handle table references and quoting.

// From src/backend/mysql/foreign_key.rs:43-100
fn prepare_foreign_key_create_statement_internal(
    &self,
    create: &ForeignKeyCreateStatement,
    sql: &mut impl SqlWriter,
    mode: Mode,
) {
    if mode == Mode::Alter {
        sql.write_str("ALTER TABLE ").unwrap();
        // ... table identification ...
    }
    if mode != Mode::Creation {
        sql.write_str("ADD ").unwrap();
    }
    sql.write_str("CONSTRAINT ").unwrap();
    // ... name, column, and reference definition ...
}

Sources: src/backend/mysql/foreign_key.rs:43-100, src/backend/postgres/foreign_key.rs:32-80

Integration with Table Builder

The TableBuilder trait coordinates foreign key definitions during table lifecycle events. When a table is created, the builder iterates through all defined foreign keys and calls the internal creation logic with Mode::Creation.

This sequence shows how the TableBuilder delegates foreign key SQL generation to the ForeignKeyBuilder during table creation.
Sources: src/backend/table_builder.rs:53-62, src/backend/foreign_key_builder.rs:60-65

Backend Specific Constraints

Backends impose different restrictions on how foreign keys are managed:

1. MySQL: Supports standalone ADD CONSTRAINT and DROP FOREIGN KEY. It requires "naked" table names (no schema) in certain foreign key contexts.
2. PostgreSQL: Supports standalone ADD CONSTRAINT and DROP CONSTRAINT. It supports qualified (schema-prefixed) table names.
3. SQLite: Does not support adding or dropping foreign keys on existing tables. Foreign keys must be defined within the CREATE TABLE statement.

Sources: src/backend/mysql/foreign_key.rs:5-15, src/backend/postgres/foreign_key.rs:90-100, README.md:580-600

Foreign Key Dropping

Dropping a foreign key involves identifying the constraint by name and the table it belongs to.

Method	MySQL Result	Postgres Result
ForeignKey::drop()	ALTER TABLE ... DROP FOREIGN KEY	ALTER TABLE ... DROP CONSTRAINT

The ForeignKeyDropStatement structure is used to build these queries. The Mode enum ensures that if the drop is part of a larger TableAlterStatement, the ALTER TABLE prefix is correctly handled or omitted.
Sources: src/backend/mysql/foreign_key.rs:21-41, src/backend/postgres/foreign_key.rs:5-30, src/backend/foreign_key_builder.rs:25-32

Summary

Foreign Key Management in SeaQuery provides a robust abstraction for maintaining referential integrity across MySQL, Postgres, and SQLite. By centralizing the logic in the ForeignKeyBuilder trait and utilizing a shared Mode system, the library ensures that constraints are correctly formatted whether they are part of a table's initial schema or added later via migrations. Significant differences, such as SQLite's lack of ALTER TABLE support for constraints, are handled by the library's high-level API to prevent invalid SQL generation.
Sources: src/lib.rs:560-620, src/backend/table_builder.rs:280-290