Foreign keys

A foreign key links one or more columns to another table. The common case — a single column referencing another table’s id — you author inline with s.references(table), which emits a text column plus a FOREIGN KEY constraint; composite and non-id keys use the table-level .foreignKey(...). This guide covers both.

A single foreign key

s.references(table) declares a column that references another table’s primary key:

TypeScript 
import { defineTable, s } from "@schemic/postgres";

export const usr = defineTable("usr", { name: s.text() });

export const post = defineTable("post", {
  author: s.references("usr"),
});
Generated DDL PostgreSQL 
CREATE TABLE "post" (
  "id" text PRIMARY KEY,
  "author" text NOT NULL
);
CREATE TABLE "usr" (
  "id" text PRIMARY KEY,
  "name" text NOT NULL
);
ALTER TABLE "post" ADD CONSTRAINT "post_author_fkey" FOREIGN KEY ("author") REFERENCES "usr" ("id");

The foreign key is emitted as its own ALTER TABLE ... ADD CONSTRAINT after both tables, so the order of references between tables never matters — even a mutual reference resolves.

Referential actions

Pass onDelete and onUpdate to control what happens when the referenced row changes. Actions are emitted uppercased; the default NO ACTION is omitted:

TypeScript 
export const post = defineTable("post", {
  author: s.references("usr", { onDelete: "cascade", onUpdate: "restrict" }),
});
Generated DDL PostgreSQL 
ALTER TABLE "post" ADD CONSTRAINT "post_author_fkey" FOREIGN KEY ("author") REFERENCES "usr" ("id") ON DELETE CASCADE ON UPDATE RESTRICT;

Referencing a table object

When you already have the target table in scope, table.record(opts?) is a typed alternative to passing the name as a string — it returns the same s.references field:

TypeScript 
const usr = defineTable("usr", { name: s.text() });

const post = defineTable("post", {
  author: usr.record({ onDelete: "cascade" }),
});
Generated DDL PostgreSQL 
ALTER TABLE "post" ADD CONSTRAINT "post_author_fkey" FOREIGN KEY ("author") REFERENCES "usr" ("id") ON DELETE CASCADE;

Composite and non-id foreign keys

s.references is the sugar for the common case — a single column pointing at another table’s id. For a multi-column key, or one that targets a non-id column, use the table-level .foreignKey(...):

TypeScript 
const team = defineTable("team", { org_id: s.text(), code: s.text() })
  .primaryKey("org_id", "code");

const member = defineTable("member", { org_id: s.text(), team_code: s.text() })
  .foreignKey({
    columns: ["org_id", "team_code"],
    refTable: "team",
    refColumns: ["org_id", "code"],
    onDelete: "cascade",
  });
Generated DDL PostgreSQL 
ALTER TABLE "member" ADD CONSTRAINT "member_org_id_team_code_fkey" FOREIGN KEY ("org_id", "team_code") REFERENCES "team" ("org_id", "code") ON DELETE CASCADE;

To reference a non-id column — here a UNIQUE one — point refColumns at it:

TypeScript 
const u = defineTable("u", { email: s.text().$unique() });

const prof = defineTable("prof", { email: s.text() })
  .foreignKey({ columns: ["email"], refTable: "u", refColumns: ["email"] });
Generated DDL PostgreSQL 
ALTER TABLE "prof" ADD CONSTRAINT "prof_email_fkey" FOREIGN KEY ("email") REFERENCES "u" ("email");

.foreignKey({ columns, refTable, refColumns?, onDelete?, onUpdate?, name? }) is the general form: refColumns defaults to ["id"], and the constraint name defaults to <table>_<columns>_fkey.

Putting it together

Foreign keys compose with checks, defaults, generated columns, and indexes in one domain:

TypeScript 
import { defineTable, s, sqlExpr } from "@schemic/postgres";

const customer = defineTable("customer", {
  email: s.text().$unique().$check(sqlExpr("email ~* '^[^@]+@[^@]+$'")),
  name: s.text(),
});

const order = defineTable("order", {
  customer: customer.record({ onDelete: "cascade" }),
  quantity: s.integer().$check(sqlExpr("quantity > 0")),
  unitPrice: s.numeric(10, 2),
  total: s.numeric(12, 2).$generated('quantity * "unitPrice"'),
  createdAt: s.timestamptz().$default(sqlExpr("now()")),
});
Generated DDL PostgreSQL 
CREATE TABLE "customer" (
  "id" text PRIMARY KEY,
  "email" text NOT NULL CHECK (email ~* '^[^@]+@[^@]+$'),
  "name" text NOT NULL
);
CREATE TABLE "order" (
  "id" text PRIMARY KEY,
  "createdAt" timestamp with time zone NOT NULL DEFAULT now(),
  "customer" text NOT NULL,
  "quantity" integer NOT NULL CHECK (quantity > 0),
  "total" numeric(12, 2) NOT NULL GENERATED ALWAYS AS (quantity * "unitPrice") STORED,
  "unitPrice" numeric(10, 2) NOT NULL
);
CREATE UNIQUE INDEX "customer_email_key" ON "customer" ("email");
ALTER TABLE "order" ADD CONSTRAINT "order_customer_fkey" FOREIGN KEY ("customer") REFERENCES "customer" ("id") ON DELETE CASCADE;

Where to go next