Constraints at Commit Time, Not at Row Time

Every bulk load that ever died halfway through left the same mess behind, ten thousand rows written, four hundred rejected, and a log file that told you which rows failed but nothing about what to do with the ones that made it, and the reason that mess keeps happening is that most engines treat a batch as a sequence of small bets rather than a unit of correctness, checking each row as it arrives and stopping the first time something looks wrong. I spent years loading data through engines that worked this way, and the ritual was always the same, split the file, run the import, grep the error log, fix the offenders, re-run the tail, because the database’s idea of integrity was a per-statement interrupt, not a per-transaction verdict.

There is a second place the same mistake hides, and that is the application layer, where the validation rules live in the web framework’s model callbacks or the form handler somebody wrote in 2009, and every cron job, backfill script, and second service that touches the same tables quietly bypasses them. We accepted that arrangement because the alternative was writing the same rules twice, once in PHP for the form and once in SQL for the trigger nobody wanted to maintain, and the predictable result was that the rules in the database and the rules in the application drifted apart until neither one was the truth. MongrelDB’s position is that the engine is the only place that sees every writer, so the engine is where the constraint set lives, and the interesting design decision is not the list of constraint kinds, it is the moment the engine chooses to enforce them.

What the commit pass actually checks

The constraint set is declared per table and it is opt-in, which matters more than it sounds, because a table with no constraints pays exactly nothing; the commit path checks the catalog first and returns immediately when no live table carries a constraint set, so every legacy table and every table that never asked for this behaves byte-for-byte as it did before. The kinds themselves are the ones a relational engine should have, unique constraints on one column or several, foreign keys with RESTRICT, CASCADE, and SET NULL on both delete and update, and CHECK constraints evaluated under SQL’s three-valued logic, where False rejects the row but Unknown passes it, which is the rule that keeps a nullable column from becoming a trap the first time a NULL flows through an expression.

CREATE TABLE inventory (
    id BIGINT PRIMARY KEY,
    price BIGINT CHECK (price >= 0),
    quantity BIGINT,
    label VARCHAR CHECK (label IS NULL OR label <> ''),
    CONSTRAINT total_limit CHECK (quantity IS NULL OR price * quantity <= 1000)
);

ALTER TABLE inventory
    ADD CONSTRAINT sku_shape CHECK (label ~* '^[a-z0-9-]+$');

The CHECK support runs deeper than a comparison or two, since the expression form covers arithmetic, boolean composition, null tests, and regex matching with the PostgreSQL-style operators ~, ~*, !~, and !~*, and a malformed pattern is rejected at DDL time instead of being discovered on the first insert. Adding a constraint to a table that already has rows validates the existing data before the schema change lands, so ALTER TABLE ... ADD CONSTRAINT fails cleanly against a dirty table rather than installing a rule the current contents already break, which is the difference between learning about your bad rows at migration time and learning about them in production.

Unique and foreign-key constraints are declared on the table schema rather than through core SQL DDL, and the Kit layer is where that declaration gets ergonomic, because the same helpers give you not-null, type, range, and string validation alongside the relational constraints, all riding on the same transaction machinery. The declaration also carries on_update actions next to the on_delete ones, so a re-keyed parent row fans out to its children with the same cascade, set-null, or restrict semantics; the example below shows the delete side, which is the side you reach for most often.

import { table, int, unique, foreignKey, check } from '@visorcraft/mongreldb-kit';

export const orderItems = table('order_items', {
  columns: [
    int('id', { primaryKey: true }),
    int('order_id', { nullable: false }),
    int('product_id', { nullable: false }),
    int('quantity', { nullable: false }),
  ],
  primaryKey: 'id',
  foreignKeys: [
    foreignKey(['order_id'], { table: 'orders', columns: ['id'] }, { onDelete: 'cascade' }),
    foreignKey(['product_id'], { table: 'products', columns: ['id'] }, { onDelete: 'restrict' }),
  ],
  unique: [unique(['order_id', 'product_id'])],
  checks: [check('qty_positive', (row) => (row.quantity as bigint) > 0n)],
});

The Kit’s int() maps to the engine’s int64, so values arrive in TypeScript as bigint and the check callback does its arithmetic in bigint terms, while the SQL surface spells the same column type BIGINT; the two names are the same integer underneath, one spelled for the DDL and one for the DSL.

Why commit time wins for batches

The enforcement point is the commit path, and it works over the whole staged transaction at once, which is the property per-statement checking can never give you. When a transaction commits, the engine runs a validation pass over everything the transaction staged, under the transaction’s read snapshot and outside the WAL mutex, and the first violation aborts the commit atomically, so the batch is either fully valid and fully durable or it never happened, and there is no middle state where half the import is visible to readers while the other half sits in an error log. Referential actions are expanded inside that same pass rather than executed as separate afterthoughts, so an ON DELETE CASCADE appends the child deletes to the staged set and keeps walking until the fixpoint settles, an ON DELETE SET NULL appends the child updates, and an update that changes a referenced key fans out to the children while the engine still holds both the old row image and the new one, which is the moment the action choice is unambiguous.

This is the part that changes how you write the import, because the failure mode of per-statement enforcement was never the error itself, it was the residue; with commit-time enforcement the transaction is the unit of correctness, and a violating batch costs you one aborted commit and a constraint name, not an afternoon of surgical deletes against rows that should never have landed. The error surfacing at commit also carries the right granularity for retries, since an idempotent batch that fails validation can be fixed and re-sent whole, and there is no partial application to reconcile against the retry.

The race you cannot check away

A uniqueness check that only reads is optimistic by construction, because two concurrent transactions can each scan, each see no conflict, and each proceed to insert the same key, and no amount of careful existence-checking inside either one closes that window. MongrelDB closes it at the commit point by claiming the key, so every staged put or update that touches a primary key or a declared unique constraint takes an exclusive claim on the encoded key, acquired in ascending key order so the claims cannot cycle into a deadlock, and the first transaction to commit wins while the second one gets the conflict, which turns the write-write race into a serialization point instead of a silent duplicate. The two mechanisms divide the work cleanly, the existence scan settles conflicts with everything already committed and deduplicates rows staged inside the same transaction, while the claim settles the conflict with a transaction that is committing concurrently right now, so neither one is redundant and neither one alone is sufficient. Null components skip the unique claim entirely, per SQL semantics, so a nullable email column does not collapse every guest checkout onto one constraint violation. The foreign-key side of the race gets the same treatment, because a commit that is checking its child rows takes parent-protection holds on the referenced keys while the checks run, which means a concurrent transaction cannot delete or re-key the parent out from under an in-flight child and leave an orphan the snapshot never saw coming.

That claim machinery is also why the constraint belongs in the engine and not in the client, since the commit path is the single authority point that every writer passes through; two remote writers over HTTP can each pass their own application-side checks and still cannot both commit a violating batch, because the second one to arrive at the fence loses. Client-side validation remains useful for error messages and UX, but it is a courtesy layer, and treating it as the enforcement layer is how you end up with two active subscriptions on one card.

What it costs

The honest cost is that validation now happens where the data is biggest, at the end of the transaction instead of at the start of the statement, so a failing batch has done its staging work before the verdict arrives, and the existence scans behind unique and foreign-key checks read against a snapshot of the table rather than a hashmap in your process. A hot unique column becomes a genuine serialization point, because the key claims are exclusive and ordered, which is correct and also means a hundred concurrent transactions inserting into the same narrow key space will queue rather than interleave; that is the price of the guarantee, and it is the same price every engine that actually keeps this promise pays, whether the invoice shows up as lock waits, as retries, or as a uniqueness index build that fails at the end. The other tradeoff is error locality, since a violation reports the constraint name at commit rather than the statement index that introduced the row, so for a fifty-statement batch you get the rule that broke and fix the input, rather than a line number into a generated SQL stream you never wanted to read anyway.

For single-row autocommit writes none of this changes anything measurable, because a one-statement transaction commits immediately and the same checks run in the same order, so the per-row and per-transaction designs only diverge when a batch is on the table, and the batch is exactly where per-row enforcement was lying to you. The opt-in flag exists because the scan is not free, and the right default for a scratch table full of intermediate results is no constraint set at all, while the right default for the table your billing code reads is the full set, and the engine lets you make that call per table instead of once for the whole database.

The modern equivalent of the old ritual, split the file and grep the error log, is no ritual at all, because the unit of work you submit is the unit of correctness you get back; we validated in application code then because the database would not hold the rules, and the database holds them now, at the one moment in the transaction’s life when the whole truth of the batch is on the table at once.