Foreign Key Support

Saferis provides a type-safe Schema builder for defining foreign key constraints. The builder uses Scala 3 macros to extract column names at compile time, ensuring type safety and catching errors early.

Basic Foreign Keys

Define a foreign key using Schema[A].withForeignKey(_.column).references[Table](_.column):

Schema[FkOrder]
  .withForeignKey(_.userId)
  .references[FkUser](_.id)
  .ddl()
  .sql

create table if not exists "fk_orders" ("id" integer generated always as identity primary key not null, "userId" integer not null, "amount" numeric not null, foreign key ("userId") references "fk_users" ("id"))

{
  // Build the instance for use with ddl.createTable
  val orders = Schema[FkOrder]
    .withForeignKey(_.userId)
    .references[FkUser](_.id)
    .build

  // Create tables with foreign key constraint
  (for
    _      <- ddl.createTable[FkUser](ifNotExists = true)
    _      <- ddl.createTable(orders)
    _      <- dml.insert(FkUser(-1, "Alice"))
    _      <- dml.insert(FkOrder(-1, 1, BigDecimal(99.99)))
    result <- sql"SELECT * FROM ${Table[FkOrder]}".query[FkOrder]
  yield result).either
    .provideLayer(DocsTransactor.layer)
}

Right(Chunk(FkOrder(1,1,99.99)))

ON DELETE and ON UPDATE Actions

Specify what happens when a referenced row is deleted or updated:

Schema[ActionOrder]
  .withForeignKey(_.userId)
  .references[ActionUser](_.id)
  .onDelete(Cascade)
  .ddl()
  .sql

create table if not exists "action_orders" ("id" integer generated always as identity primary key not null, "userId" integer not null, foreign key ("userId") references "action_users" ("id") on delete cascade)

Schema[ActionOrder]
  .withForeignKey(_.userId)
  .references[ActionUser](_.id)
  .onDelete(SetNull)
  .ddl()
  .sql

create table if not exists "action_orders" ("id" integer generated always as identity primary key not null, "userId" integer not null, foreign key ("userId") references "action_users" ("id") on delete set null)

Available actions (import saferis.Schema.* to use short names):

Action	Description
`NoAction`	Fail if referenced row is deleted/updated (default)
`Cascade`	Delete/update child rows when parent is deleted/updated
`SetNull`	Set the FK column to NULL
`SetDefault`	Set the FK column to its default value
`Restrict`	Fail immediately (same as NoAction but checked immediately)

Named Constraints

Give your foreign key constraint a custom name:

Schema[NamedOrder]
  .withForeignKey(_.userId)
  .references[NamedUser](_.id)
  .onDelete(Cascade)
  .named(ConstraintName("fk_order_user"))
  .ddl()
  .sql

create table if not exists "named_orders" ("id" integer generated always as identity primary key not null, "userId" integer not null, constraint "fk_order_user" foreign key ("userId") references "named_users" ("id") on delete cascade)

Compound Foreign Keys

Reference a composite primary key with multiple columns using .and():

Schema[CompoundInventory]
  .withForeignKey(_.tenantId)
  .and(_.productSku)
  .references[CompoundProduct](_.tenantId)
  .and(_.sku)
  .onDelete(Cascade)
  .ddl()
  .sql

create table if not exists "compound_inventory" ("id" integer generated always as identity primary key not null, "tenantId" varchar(255) not null, "productSku" varchar(255) not null, "quantity" integer not null, foreign key ("tenantId", "productSku") references "compound_products" ("tenantId", "sku") on delete cascade)

{
  // Build and create tables with compound FK
  val inventory = Schema[CompoundInventory]
    .withForeignKey(_.tenantId)
    .and(_.productSku)
    .references[CompoundProduct](_.tenantId)
    .and(_.sku)
    .onDelete(Cascade)
    .build

  (for
    _      <- ddl.createTable[CompoundProduct](ifNotExists = true)
    _      <- ddl.createTable(inventory)
    _      <- dml.insert(CompoundProduct("tenant1", "SKU-001", "Widget"))
    _      <- dml.insert(CompoundInventory(-1, "tenant1", "SKU-001", 100))
    result <- sql"SELECT * FROM ${Table[CompoundInventory]}".query[CompoundInventory]
  yield result).either
    .provideLayer(DocsTransactor.layer)
}

Right(Chunk(CompoundInventory(1,tenant1,SKU-001,100)))

Multiple Foreign Keys

Chain multiple foreign keys using .withForeignKey():

Schema[MultiOrderItem]
  .withForeignKey(_.userId)
  .references[MultiUser](_.id)
  .onDelete(Cascade)
  .withForeignKey(_.productId)
  .references[MultiProduct](_.id)
  .onDelete(Restrict)
  .ddl()
  .sql

create table if not exists "multi_order_items" ("id" integer generated always as identity primary key not null, "userId" integer not null, "productId" integer not null, "quantity" integer not null, foreign key ("userId") references "multi_users" ("id") on delete cascade, foreign key ("productId") references "multi_products" ("id") on delete restrict)

Compile-Time Column Safety

The foreign key builder extracts column names from selectors at compile time, so the source and target columns must actually exist on their tables:

{
  // This compiles - userId and id are both real columns
  val valid = Schema[TypeOrder]
    .withForeignKey(_.userId)
    .references[TypeUser](_.id)
  valid.ddl().sql
}

create table if not exists "type_orders" ("id" integer generated always as identity primary key not null, "userId" integer not null, "userName" varchar(255) not null, foreign key ("userId") references "type_users" ("id"))

Referencing a column that doesn't exist on the source table is a compile error. The snippet below does not compile; the actual compiler diagnostic is shown beneath it:

import saferis.*
import saferis.Schema.*

@tableName("type_users")
case class TypeUser(@generated @key id: Int, name: String) derives Table

@tableName("type_orders")
case class TypeOrder(@generated @key id: Int, userId: Int) derives Table

// `nope` is not a field of TypeOrder: compile error.
val invalid = Schema[TypeOrder]
  .withForeignKey(_.nope)
  .references[TypeUser](_.id)

value nope is not a member of TypeOrder
  .withForeignKey(_.nope).references[TypeUser](_.id)

Foreign Key Support

#Basic Foreign Keys

#ON DELETE and ON UPDATE Actions

#Named Constraints

#Compound Foreign Keys