Constraints and indices

pgcraft dimensions are defined with schema_items — a list of SQLAlchemy Column objects mixed with pgcraft constraint and index definitions. All three use {column_name} markers to reference columns, and all three are validated against the actual table columns at factory time.

Class	Purpose	Import
PGCraftCheck	CHECK constraints	from pgcraft.check import PGCraftCheck
PGCraftIndex	Indices (btree, GIN, unique, functional, …)	from pgcraft.index import PGCraftIndex
PGCraftFK	Foreign key constraints	from pgcraft.fk import PGCraftFK

Column markers

All three classes reference columns with {column_name} markers. At factory time, pgcraft validates that every referenced column exists on the target table and substitutes the markers with real column references.

# Check: the expression is a SQL predicate
PGCraftCheck("{price} > 0", name="positive_price")

# Index: each argument is an expression
PGCraftIndex("idx_name", "{name}")
PGCraftIndex("idx_lower", "lower({name})")

# FK: dict maps {local_col} to target reference
PGCraftFK(
    references={"{customer_id}": "customers.id"},
    name="fk_customer",
)

If a marker names a column that does not exist on the table, pgcraft raises PGCraftValidationError at factory time — not at migration time or at runtime.

Check constraints

PGCraftCheck defines a SQL CHECK constraint. It takes an expression and a name.

PGCraftCheck("{price} > 0", name="positive_price")
PGCraftCheck(
    "{end_date} > {start_date}",
    name="valid_date_range",
)

For simple and append-only dimensions, this becomes a real CHECK constraint on the table. For EAV dimensions, it becomes a trigger function that validates NEW.price > 0 before the main EAV triggers process the row. The same PGCraftCheck definition works on all dimension types — pgcraft picks the right enforcement strategy automatically.

Indices

PGCraftIndex mirrors the sqlalchemy.Index constructor: name first, then column expressions, then keyword arguments passed through to the underlying index.

# Simple index
PGCraftIndex("idx_products_sku", "{sku}")

# Unique index
PGCraftIndex("uq_products_name", "{name}", unique=True)

# Functional index with dialect kwargs
PGCraftIndex(
    "idx_lower_name", "lower({name})",
    postgresql_using="btree",
)

# Multi-column index
PGCraftIndex("idx_name_price", "{name}", "{price}")

PGCraftIndex supports the same keyword arguments as sqlalchemy.Index:

Keyword	Effect
unique=True	Creates a UNIQUE index
postgresql_using="gin"	Uses the GIN index method
postgresql_where=text("active")	Partial index (WHERE active)
postgresql_ops={"data": "jsonb_path_ops"}	Operator class for a column

Foreign keys

PGCraftFK defines foreign key constraints. Each entry in the dict maps a {local_col} marker to its target reference — keeping the column and its target together, like SQLAlchemy’s ForeignKey("customers.id").

references vs raw_references

Exactly one must be provided:

Parameter	Format	When to use
references	{"{col}": "dimension.column"}	Target is a pgcraft dimension. Resolved via the dimension registry at factory time.
raw_references	{"{col}": "schema.table.column"}	Target is outside pgcraft, or you want full control. Passed through to SQLAlchemy as-is.

# Resolved — pgcraft finds the physical table
PGCraftFK(
    references={"{customer_id}": "customers.id"},
    name="fk_orders_customer",
    ondelete="CASCADE",
)

# Raw — passed through directly
PGCraftFK(
    raw_references={"{org_id}": "tenant.orgs.id"},
    name="fk_orders_org",
)

# Multi-column
PGCraftFK(
    raw_references={
        "{tenant_id}": "shared.orgs.tenant_id",
        "{org_id}": "shared.orgs.org_id",
    },
    name="fk_composite",
)

Cascade options

Both ondelete and onupdate accept any PostgreSQL action: CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION (the default).

PGCraftFK(
    references={"{customer_id}": "customers.id"},
    name="fk_orders_customer",
    ondelete="CASCADE",
    onupdate="SET NULL",
)

Simple dimension example

customers = PGCraftSimple(
    tablename="customers",
    schemaname="public",
    metadata=metadata,
    schema_items=[
        Column("name", String, nullable=False),
        Column("email", String, nullable=False),
        PGCraftIndex("uq_customers_email", "{email}", unique=True),
    ],
)

orders = PGCraftSimple(
    tablename="orders",
    schemaname="public",
    metadata=metadata,
    schema_items=[
        Column("customer_id", Integer, nullable=False),
        Column("total", Numeric(10, 2), nullable=False),
        Column("status", String, nullable=False),
        PGCraftCheck("{total} > 0", name="positive_total"),
        PGCraftCheck(
            "{status} IN ('pending', 'paid', 'cancelled')",
            name="valid_status",
        ),
        PGCraftIndex("idx_orders_customer_id", "{customer_id}"),
        PGCraftIndex("idx_orders_status", "{status}"),
        PGCraftFK(
            references={"{customer_id}": "customers.id"},
            name="fk_orders_customer",
            ondelete="CASCADE",
        ),
    ],
)

Schema

Two simple tables: public.customers with a unique index on email, and public.orders with check constraints, indices, and a foreign key to customers.

=# \d public.customers
                                 Table "public.customers"
 Column |       Type        | Collation | Nullable |                Default
--------+-------------------+-----------+----------+---------------------------------------
 id     | integer           |           | not null | nextval('customers_id_seq'::regclass)
 name   | character varying |           | not null |
 email  | character varying |           | not null |
Indexes:
    "pk__customers__id" PRIMARY KEY, btree (id)
    "uq_customers_email" UNIQUE, btree (email)
Referenced by:
    TABLE "orders" CONSTRAINT "fk_orders_customer" FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE

=# \d public.orders
                                    Table "public.orders"
   Column    |       Type        | Collation | Nullable |              Default
-------------+-------------------+-----------+----------+------------------------------------
 id          | integer           |           | not null | nextval('orders_id_seq'::regclass)
 customer_id | integer           |           | not null |
 total       | numeric(10,2)     |           | not null |
 status      | character varying |           | not null |
Indexes:
    "pk__orders__id" PRIMARY KEY, btree (id)
    "idx_orders_customer_id" btree (customer_id)
    "idx_orders_status" btree (status)
Check constraints:
    "positive_total" CHECK (total > 0::numeric)
    "valid_status" CHECK (status::text = ANY (ARRAY['pending'::character varying, 'paid'::character varying, 'cancelled'::character varying]::text[]))
Foreign-key constraints:
    "fk_orders_customer" FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE

Sample queries

=# SELECT * FROM public.customers;
 id | name  |       email
----+-------+-------------------
  1 | Alice | alice@example.com
  2 | Bob   | bob@example.com
(2 rows)

=# SELECT * FROM public.orders;
 id | customer_id | total  |  status
----+-------------+--------+-----------
  1 |           1 |  49.99 | paid
  2 |           2 | 120.00 | pending
  3 |           1 |  15.50 | cancelled
(3 rows)

Inserting an order with a negative total violates the positive_total check constraint.

=# INSERT INTO public.orders (customer_id, total, status) VALUES (1, -5, 'pending');
ERROR:  new row for relation "orders" violates check constraint "positive_total"
DETAIL:  Failing row contains (1, 1, -5.00, pending).

Inserting an order with a nonexistent customer violates the foreign key.

=# INSERT INTO public.orders (customer_id, total, status) VALUES (999, 10, 'pending');
ERROR:  duplicate key value violates unique constraint "pk__orders__id"
DETAIL:  Key (id)=(2) already exists.

Append-only dimension example

Constraints and indices on append-only dimensions are placed on the attributes table. Foreign keys that target an append-only dimension resolve to the root table — the stable primary key.

departments = PGCraftSimple(
    tablename="departments",
    schemaname="public",
    metadata=metadata,
    schema_items=[
        Column("name", String, nullable=False),
        PGCraftIndex("uq_departments_name", "{name}", unique=True),
    ],
)

employees = PGCraftAppendOnly(
    tablename="employees",
    schemaname="public",
    metadata=metadata,
    schema_items=[
        Column("name", String, nullable=False),
        Column("salary", Integer, nullable=False),
        Column("department_id", Integer, nullable=False),
        PGCraftCheck("{salary} > 0", name="positive_salary"),
        PGCraftIndex(
            "idx_employees_department_id",
            "{department_id}",
        ),
        PGCraftFK(
            references={"{department_id}": "departments.id"},
            name="fk_employees_department",
        ),
    ],
)

Schema

An append-only employees dimension with a check constraint, index, and foreign key to a simple departments dimension. The FK resolves to the departments table via the dimension registry. Constraints and indices are placed on the attributes table.

=# \d public.departments
                                 Table "public.departments"
 Column |       Type        | Collation | Nullable |                 Default
--------+-------------------+-----------+----------+-----------------------------------------
 id     | integer           |           | not null | nextval('departments_id_seq'::regclass)
 name   | character varying |           | not null |
Indexes:
    "pk__departments__id" PRIMARY KEY, btree (id)
    "uq_departments_name" UNIQUE, btree (name)
Referenced by:
    TABLE "employees_attributes" CONSTRAINT "fk_employees_department" FOREIGN KEY (department_id) REFERENCES departments(id)

=# \d public.employees_attributes
                                             Table "public.employees_attributes"
    Column     |           Type           | Collation | Nullable |                          Default
---------------+--------------------------+-----------+----------+-----------------------------------------------------------
 id            | integer                  |           | not null | nextval('employees_attributes_id_seq'::regclass)
 created_at    | timestamp with time zone |           |          | '2026-03-15 23:17:27.065693+00'::timestamp with time zone
 name          | character varying        |           | not null |
 salary        | integer                  |           | not null |
 department_id | integer                  |           | not null |
Indexes:
    "pk__employees_attributes__id" PRIMARY KEY, btree (id)
    "idx_employees_department_id" btree (department_id)
Foreign-key constraints:
    "fk_employees_department" FOREIGN KEY (department_id) REFERENCES departments(id)
Referenced by:
    TABLE "employees_root" CONSTRAINT "fk__employees_root__employees_attributes_id__employees_dee9c74f" FOREIGN KEY (employees_attributes_id) REFERENCES employees_attributes(id)

=# \d public.employees_root
                                                     Table "public.employees_root"
         Column          |           Type           | Collation | Nullable |                          Default
-------------------------+--------------------------+-----------+----------+-----------------------------------------------------------
 id                      | integer                  |           | not null | nextval('employees_root_id_seq'::regclass)
 created_at              | timestamp with time zone |           |          | '2026-03-15 23:17:27.065693+00'::timestamp with time zone
 employees_attributes_id | integer                  |           |          |
Indexes:
    "pk__employees_root__id" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk__employees_root__employees_attributes_id__employees_dee9c74f" FOREIGN KEY (employees_attributes_id) REFERENCES employees_attributes(id)

Sample queries

=# SELECT * FROM public.departments;
 id |    name
----+-------------
  1 | Engineering
  2 | Marketing
(2 rows)

The attributes table has the check constraint, index, and FK.

=# SELECT * FROM public.employees_attributes ORDER BY id;
 id |          created_at           | name  | salary | department_id
----+-------------------------------+-------+--------+---------------
  1 | 2026-03-15 23:17:27.065693+00 | Alice |  95000 |             1
  2 | 2026-03-15 23:17:27.065693+00 | Bob   |  72000 |             2
(2 rows)

Inserting an employee with a negative salary violates the positive_salary check constraint.

=# INSERT INTO public.employees_attributes (name, salary, department_id) VALUES ('Charlie', -100, 1);
ERROR:  duplicate key value violates unique constraint "pk__employees_attributes__id"
DETAIL:  Key (id)=(1) already exists.

Inserting with a nonexistent department violates the foreign key.

=# INSERT INTO public.employees_attributes (name, salary, department_id) VALUES ('Charlie', 50000, 999);
ERROR:  duplicate key value violates unique constraint "pk__employees_attributes__id"
DETAIL:  Key (id)=(2) already exists.

EAV dimension example

EAV dimensions store attributes as rows, not columns. Table-level CHECK constraints cannot reference virtual columns, so pgcraft enforces checks via INSTEAD OF trigger functions instead. The same PGCraftCheck syntax works — the enforcement mechanism is chosen automatically.

products = PGCraftEAV(
    tablename="products",
    schemaname="private",
    metadata=metadata,
    schema_items=[
        Column("color", String),
        Column("weight", Float),
        Column("price", Integer),
        PGCraftCheck("{price} > 0", name="positive_price"),
        PGCraftCheck("{weight} > 0", name="positive_weight"),
    ],
)

APIView(source=products)