Foreign keys

Foreign keys are defined in the constraints object of table definition using the foreignKey function.

Single column

import { integer, foreignKey, primaryKey, table } from "@monolayer/pg/schema";

const users = table({ 
  columns: {
    id: integer().generatedAlwaysAsIdentity(), 
  },
  constraints: {
    primaryKey: primaryKey(["id"]),
  },
});

const documents = table({
  columns: {
    id: integer().generatedAlwaysAsIdentity(),
    userId: integer(),
  },
  constraints: {
    primaryKey: primaryKey(["id"]),
    foreignKeys: [ 
      foreignKey(["userId"], users, ["id"]), 
    ], 
  },
});

Multiple of columns

import {
  integer,
  foreignKey,
  primaryKey,
  table,
  timestampWithTimeZone
} from "@monolayer/pg/schema";

const projects = table({ 
  columns: {
    id: integer(), 
    departmendId: integer(), 
  },
  constraints: {
    primaryKey: primaryKey(["id", "departmentId"]),
  },
});

const assignments = table({
  columns: {
    dueDate: timestampWithTimeZone(),
    projectId: integer().generatedAlwaysAsIdentity(),
    departmentId: integer(),
  },
  constraints: {
    foreignKeys: [ 
      foreignKey( 
        ["projectId", "departmentId"], 
        projects, 
        ["id", "departmentId"] 
      ),
    ], 
  },
});

With actions

When you define a foreign key constraint, you can specify actions to taken when the referenced row in the parent table is deleted or updated. There are five actions that you can set when a referenced row is updated or deleted:

ACTION	Behavior
CASCADE	When a referenced row is deleted, row(s) referencing it should be automatically deleted as well
SET NULL	Referencing column(s) in the referencing row(s) will be set to NULL when the referenced row is deleted or when the referenced row key is updated
SET DEFAULT	Referencing column(s) in the referencing row(s) will be set to their default value when the referenced row is deleted or when the referenced row key is updated
RESTRICT	Prevents the deletion of a referenced row up updated of a referenced key
NO ACTION	If any referencing rows still exist when the constraint is checked, an error is raised. Similar to RESTRICT. The difference is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not

INFO

The default action for a foreign key is NO ACTION.

Read more about foreign keys in PostgreSQL - Foreign Keys

Use the modifiers deleteRule and updateRule to specify the foreign key actions:

const documents = table({
  columns: {
    id: integer().generatedAlwaysAsIdentity(),
    userId: integer(),
  },
  constraints: {
    primaryKey: primaryKey(["id"]),
    foreignKeys: [
      foreignKey(["userId"], users, ["id"]) 
        .deleteRule("set null") 
        .updateRule("cascade"), 
    ],
  },
});

Self-referential

import { integer, foreignKey, primaryKey, table } from "@monolayer/pg/schema";

const tree = table({
  columns: {
    nodeId: integer().generatedAlwaysAsIdentity(), 
    parentId: integer(), 
  },
  constraints: {
    primaryKey: primaryKey(["nodeId"]),
    foreignKeys: [ 
      foreignKey(["parentId"], ["nodeId"]), 
    ], 
  },
});