Phase 7: Foreign Key and Index Support for Migrations

Overview

Add comprehensive foreign key and simplified index support to the migration/schema system with a fluent API that allows defining FK constraints and indexes inline with column definitions.

Design Goals

Fluent column-level API - Define FKs and indexes directly on column builder
Method order independence - Chain methods in any order
Automatic name generation - Generate constraint/index names when not explicitly provided
ALTER TABLE support - Add FKs/indexes when adding columns, drop by name or column
Cross-dialect support - Handle SQLite limitations transparently
Consistent API - FKs and indexes follow the same patterns

API Design

Column-Level Foreign Key API

The primary API for defining foreign keys is inline with column definitions:

// Basic FK with auto-generated name
t.column<int64?>("user_id")
    .not_null()
    .references("users", "id");

// Full FK with all options
t.column<int64?>("user_id")
    .references("users", "id")
        .name("fk_orders_users")           // optional, auto-generated if omitted
        .on_delete_cascade()               // ON DELETE CASCADE
        .on_delete_set_null()              // ON DELETE SET NULL
        .on_delete_set_default()           // ON DELETE SET DEFAULT
        .on_delete_no_action()             // ON DELETE NO ACTION (default)
        .on_delete_restrict()              // ON DELETE RESTRICT
        .on_update_cascade()               // ON UPDATE CASCADE
        .on_update_set_null()              // etc.
        .on_update_set_default()
        .on_update_no_action()             // default
        .on_update_restrict()
    .not_null();                           // Can continue with column methods

Column-Level Index API

For simple single-column indexes, use .indexed() directly on the column builder:

// Basic index with auto-generated name (idx_{table}_{column})
t.column<string>("email")
    .not_null()
    .indexed();

// Unique index - uniqueness inferred from column's unique() flag
t.column<string>("email")
    .not_null()
    .unique()
    .indexed();  // Creates UNIQUE index

// Index with custom name
t.column<string>("email")
    .indexed("idx_users_email_address");

Key Design Points:

indexed(string? name = null) returns MigrationColumnBuilder (same type, allows chaining)
Uniqueness is automatically inferred from the column's .unique() flag
No separate builder class needed - keeps the API simple
The existing t.index("name").on_column("col") API remains for:
- Multi-column composite indexes
- More complex index configurations
- Explicit control over index definition order

Key Design Principle: Inheritance Chain

The object returned by .references() must inherit from MigrationColumnBuilder so that:

All column methods remain available after FK configuration
Method order is flexible (.not_null() before or after .references())
The API feels natural and discoverable

For .indexed(), it simply returns this (MigrationColumnBuilder) since no additional configuration is needed.

ALTER TABLE Support

// Add column with FK
b.alter_table("orders", t => {
    t.add_column<int64?>("product_id")
        .references("products", "id")
        .on_delete_restrict();
});

// Add column with index
b.alter_table("orders", t => {
    t.add_column<string>("sku")
        .unique()
        .indexed();  // Creates UNIQUE index (uniqueness from column)
});

// Drop FK by constraint name
b.alter_table("orders", t => {
    t.drop_foreign_key("fk_orders_users");
});

// Drop FK by column (useful for auto-generated names)
b.alter_table("orders", t => {
    t.drop_foreign_key_on("user_id");
});

// Drop index by column (useful for auto-generated names)
b.alter_table("orders", t => {
    t.drop_index_on("sku");
});

Default Values

ON DELETE: NO ACTION (SQL standard default)
ON UPDATE: NO ACTION (SQL standard default)
Constraint name: Auto-generated as fk_{table}_{column} if not specified

Architecture

Class Hierarchy

MigrationColumnBuilder
    └── ForeignKeyBuilder (extends MigrationColumnBuilder)
            - Stores FK configuration
            - All column methods pass through to parent
            - FK-specific methods return this

Note: indexed() does NOT need a separate builder - it returns this (MigrationColumnBuilder)

New Classes/Files

ForeignKeyBuilder (src/migrations/foreign-key-builder.vala)
- Extends MigrationColumnBuilder
- Stores: referenced table, referenced column, constraint name, on_delete, on_update
- Provides fluent FK methods
DropForeignKeyOperation (in schema-operations.vala)
- Schema operation for dropping FK constraints
- Properties: constraint_name OR column_name (for lookup)

Modified Classes

MigrationColumnBuilder
- Add references(table, column) method returning ForeignKeyBuilder
- Add indexed(string? name = null) method returning this
- Add FK constraint storage
- Add index flag storage (index_name, should_create_index)
TableBuilder
- Collect FK constraints from column builders
- Collect index operations from column builders
- Pass to CreateTableOperation
AlterTableBuilder
- Add drop_foreign_key(name) method
- Add drop_foreign_key_on(column) method
- Add drop_index_on(column) method
TableConstraint
- Add on_delete_action property
- Add on_update_action property
SqlDialect Interface
- Add drop_foreign_key_sql(DropForeignKeyOperation op) method
SqliteDialect
- Update build_constraint_sql() to include ON DELETE/UPDATE
- Implement drop_foreign_key_sql() with table recreation

SQLite Table Recreation Strategy

SQLite does not support ALTER TABLE DROP CONSTRAINT. The dialect must handle this transparently:

-- Generated SQL for dropping FK on SQLite:
BEGIN TRANSACTION;

CREATE TABLE _orders_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    -- ... other columns without FK constraint
);

INSERT INTO _orders_new SELECT * FROM orders;

DROP TABLE orders;

ALTER TABLE _orders_new RENAME TO orders;

-- Recreate indexes
CREATE INDEX idx_orders_user_id ON orders (user_id);

COMMIT;

Implementation Approach

drop_foreign_key_sql() returns a single string with multiple statements separated by semicolons and newlines
The method needs schema introspection to:
- Get all columns and their types
- Get remaining constraints (PK, UNIQUE, other FKs)
- Get all indexes on the table
Connection.execute() already handles multi-statement SQL

Referential Actions Enum

public enum ReferentialAction {
    NO_ACTION,    // default
    CASCADE,
    SET_NULL,
    SET_DEFAULT,
    RESTRICT;
    
    public string to_sql() {
        switch (this) {
            case CASCADE: return "CASCADE";
            case SET_NULL: return "SET NULL";
            case SET_DEFAULT: return "SET DEFAULT";
            case RESTRICT: return "RESTRICT";
            default: return "NO ACTION";
        }
    }
}

Implementation Tasks

Task 1: Create ReferentialAction Enum

Add enum to src/migrations/schema-operations.vala
Include to_sql() method for SQL generation

Task 2: Update TableConstraint Class

Add on_delete_action property (ReferentialAction, default NO_ACTION)
Add on_update_action property (ReferentialAction, default NO_ACTION)

Task 3: Create ForeignKeyBuilder Class

Create src/migrations/foreign-key-builder.vala
Extend MigrationColumnBuilder
Store FK configuration (ref_table, ref_column, name, on_delete, on_update)
Implement fluent methods: name(), on_delete_*(), on_update_*()
Override/extend to pass column methods to parent

Task 4: Update MigrationColumnBuilder for Index Support

Add index flag storage (index_name, should_create_index)
Implement indexed(string? name = null) returning this
Ensure index operation is created when column is built (use column's is_unique for index uniqueness)

Task 5: Update MigrationColumnBuilder for FK Support

Add FK constraint storage
Implement references(table, column) returning ForeignKeyBuilder
Ensure FK constraint is passed to parent builder on build

Task 6: Update TableBuilder

Collect FK constraints from column definitions
Collect index operations from column definitions
Add to CreateTableOperation constraints list
Add index operations to migration

Task 7: Update AlterTableBuilder

Implement drop_foreign_key(string name) method
Implement drop_foreign_key_on(string column) method
Implement drop_index_on(string column) method
Create DropForeignKeyOperation for FK drops

Task 8: Create DropForeignKeyOperation

Add to src/migrations/schema-operations.vala
Properties: table_name, constraint_name (optional), column_name (optional)

Task 9: Update SqlDialect Interface

Add drop_foreign_key_sql(DropForeignKeyOperation op) abstract method

Task 10: Update SqliteDialect

Update build_constraint_sql() to emit ON DELETE/UPDATE clauses
Implement drop_foreign_key_sql() with table recreation logic
Implement helper to introspect current table schema for recreation

Task 11: Update Example Migrations

Update v003-create-orders.vala to use new FK API and .indexed()
Add example of ALTER TABLE with FK

Task 12: Add Tests

Test FK creation with auto-generated name
Test FK creation with explicit name
Test all referential actions
Test FK in ALTER TABLE add column
Test drop FK by name
Test drop FK by column
Test SQLite table recreation for drop FK
Test .indexed() with auto-generated name
Test .indexed("custom_name") with custom name
Test that .unique().indexed() creates UNIQUE index
Test drop_index_on()

Example Usage

Complete Migration Example

public class V003_CreateOrders : Migration {
    public override int version { get { return 3; } }
    public override string name { get { return "CreateOrders"; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("orders", t => {
            t.column<int64?>("id")
                .primary_key()
                .auto_increment();
            
            t.column<int64?>("user_id")
                .not_null()
                .references("users", "id")
                    .name("fk_orders_users")
                    .on_delete_cascade()
                .indexed();  // Auto-generates idx_orders_user_id
            
            t.column<int64?>("product_id")
                .not_null()
                .references("products", "id")
                    .on_delete_restrict()
                .indexed();  // Auto-generates idx_orders_product_id
            
            t.column<int64?>("quantity");
            t.column<double?>("total");
            t.column<string>("status")
                .indexed();  // Auto-generates idx_orders_status
            t.column<int64?>("created_at");
            
            // Multi-column indexes still use the existing API:
            // t.index("idx_composite").on_columns("col1", "col2");
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("orders");
    }
}

ALTER TABLE Example

public class V004_AddCategoryToProducts : Migration {
    public override int version { get { return 4; } }
    public override string name { get { return "AddCategoryToProducts"; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        // First create the categories table
        b.create_table("categories", t => {
            t.column<int64?>("id")
                .primary_key()
                .auto_increment();
            t.column<string>("name")
                .not_null();
        });
        
        // Then add the FK column to products
        b.alter_table("products", t => {
            t.add_column<int64?>("category_id")
                .references("categories", "id")
                .on_delete_set_null();  // Keep products when category deleted
        });
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        b.alter_table("products", t => {
            t.drop_foreign_key_on("category_id");
            t.drop_column("category_id");
        });
        b.drop_table("categories");
    }
}

Generated SQL Examples

CREATE TABLE with FK

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER,
    total REAL,
    status TEXT,
    created_at INTEGER,
    CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_orders_products FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT
)

ALTER TABLE ADD COLUMN with FK

ALTER TABLE products ADD COLUMN category_id INTEGER REFERENCES categories (id) ON DELETE SET NULL

DROP FK on PostgreSQL/MySQL

ALTER TABLE orders DROP CONSTRAINT fk_orders_users

DROP FK on SQLite

BEGIN TRANSACTION;
CREATE TABLE _orders_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER,
    total REAL,
    status TEXT,
    created_at INTEGER,
    CONSTRAINT fk_orders_products FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT
);
INSERT INTO _orders_new SELECT id, user_id, product_id, quantity, total, status, created_at FROM orders;
DROP TABLE orders;
ALTER TABLE _orders_new RENAME TO orders;
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_product_id ON orders (product_id);
CREATE INDEX idx_orders_status ON orders (status);
COMMIT;

Questions Resolved

API Style: Column-level fluent API (EF Core inspired)
Method Order: Flexible - any order works due to inheritance
Naming: Optional .name() for FKs, optional name parameter for .indexed(name); auto-generate as fk_{table}_{column} or idx_{table}_{column}
Composite FKs: Not in this phase (future enhancement)
ALTER TABLE: Full support for add/drop FKs and indexes
Drop FK: Both by name and by column
Drop Index: By column via drop_index_on()
SQLite Handling: Transparent table recreation in dialect layer
Default Actions: NO ACTION for both ON DELETE and ON UPDATE
Index API: Simple .indexed(string? name = null) returning same builder; uniqueness inferred from column's .unique() flag

phase-7-foreign-keys.md 15 KB Verlauf Originalformat