Indexes and foreign keys

Indexes make queries fast and keep values unique; foreign keys keep references between tables valid. Both are declared on the blueprint, in schema.create or schema.table, next to the columns they cover.

schema
    .create("members", |t| {
        t.id();
        t.foreign_uuid("workspace_id").constrained().cascade_on_delete();
        t.foreign_uuid("user_id").constrained_on("users", "id").cascade_on_delete();
        t.string("email");
        t.string("role").length(32).index();
        t.timestamps_tz();
        t.unique(["workspace_id", "email"]);
    })
    .await?;

Indexes

An index is declared on a column, or on the blueprint for several columns:

t.string("email").unique();                       // on the column
t.string("stage").index();
t.primary(["workspace_id", "user_id"]);           // on the blueprint
t.unique(["workspace_id", "email"]).name("members_email");
t.index(["stage", "created_at"]);
t.index("deleted_at").where_raw("deleted_at IS NULL");
t.raw_index("lower(email)", "users_email_lower_index");

columns is one name ("email") or an array (["workspace_id", "email"]). Column indexes are created after the table and in the order declared; in table, every command runs in order, so t.string("slug") followed by t.unique(["slug"]) adds the column first.

Index options

The blueprint methods return an index builder:

A partial unique index lets soft-deleted rows keep their values without blocking new ones: t.unique(["email"]).where_raw("deleted_at IS NULL") creates CREATE UNIQUE INDEX "users_email_unique" ON "users" ("email") WHERE deleted_at IS NULL.

Full-text and spatial indexes

t.fulltext(["title", "body"]).language("english");
t.spatial_index("location");

A full-text index is GIN over to_tsvector on PostgreSQL, so the ORM's where_full_text uses it, and FULLTEXT on MySQL. SQLite has no full-text index: the builder refuses it and points to an FTS5 virtual table. Spatial indexes are GIST on PostGIS columns and SPATIAL on MySQL.

Names

Indexes are named {table}_{columns}_{kind}, with primary, unique, index, fulltext, spatialindex or foreign as the kind, lower-cased, with - and . replaced by _: deals_stage_created_at_index, members_workspace_id_email_unique, deals_company_id_foreign. A name longer than 63 bytes (PostgreSQL's limit) is shortened to 54 bytes plus _ and an 8-digit hash, the same way on every database, so a drop by columns finds it. Schema::index_name(table, kind, columns) computes one.

Foreign keys

A foreign key column usually references the conventional table, by constrained():

t.foreign_uuid("company_id").constrained().cascade_on_delete();     // companies.id
t.foreign_id("user_id").nullable().constrained().null_on_delete();  // users.id
t.foreign_uuid("owner_id").constrained_on("users", "id");
t.foreign("currency").references("code").on("currencies").restrict_on_delete();

constrained() takes the table from the column's name, company_id referencing companies.id (the part before _id, plural). constrained_on(table, column) names both. t.foreign(columns).references(columns).on(table) declares one on the blueprint, for any columns, composite keys included.

ALTER TABLE "deals" ADD CONSTRAINT "deals_company_id_foreign"
  FOREIGN KEY ("company_id") REFERENCES "companies" ("id") ON DELETE CASCADE

A foreign key needs the referenced table, so order the migration sets: a deals set that references companies runs .after(["companies"]), and a table referencing users runs after "auth". On SQLite, foreign keys are part of the table, and adding or dropping one later rebuilds it. Navree's time-series tables are hypertables on TimescaleDB whose keys include the time column, so nothing may reference their id alone (TimescaleDB).

Foreign key violations answer 409 over HTTP, like unique violations (database errors).

Check constraints

A check constraint is a rule every row must meet, enforced by the database whatever writes the row. t.check(name, expression) adds one; the expression is SQL and may name several columns:

schema
    .create("discounts", |t| {
        t.id();
        t.integer("percent");
        t.date("starts_on");
        t.date("ends_on").nullable();
        t.check("discounts_percent_check", "percent BETWEEN 1 AND 100");
        t.check("discounts_period_check", "ends_on IS NULL OR ends_on >= starts_on");
    })
    .await?;

ALTER TABLE "discounts" ADD CONSTRAINT "discounts_percent_check" CHECK (percent BETWEEN 1 AND 100)

PostgreSQL, MySQL and MariaDB add the constraint with ALTER TABLE, in schema.create and schema.table alike. SQLite writes it into CREATE TABLE; adding one to an existing table, or dropping one, rebuilds the table with its rows. Adding a check that existing rows break fails, and so does a write that breaks one. MySQL parses checks before 8.0.16 without enforcing them, and the migration says so in a notice. Name each check after its table: MySQL wants constraint names unique in the whole database. Shared libraries use one to keep their owner columns consistent.

Dropping and renaming

schema
    .table("deals", |t| {
        t.drop_index(["stage", "created_at"]);     // by columns: the conventional name
        t.drop_unique("deals_code_unique");          // by name
        t.drop_foreign(["company_id"]);
        t.drop_constrained_foreign_id("owner_id");   // the foreign key and its column
        t.rename_index("deals_title_index", "deals_by_title");
        t.drop_check("deals_amount_check");          // by name
    })
    .await?;

Drops take a name or the columns, which give the conventional name: drop_index, drop_unique, drop_fulltext, drop_spatial_index, drop_foreign, drop_primary() (the conventional primary key) and drop_primary_named(name). drop_constrained_foreign_id drops a foreign key and then its column. drop_check takes the check's name. To check before dropping, schema.has_index(table, name_or_columns, kind) and schema.has_foreign_key(table, ..) read the live schema.