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
  xa.run(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
}

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("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

  xa.run(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
}

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