# unique constraint: a worked example

Canonical page: https://vibe-coding-101.brighteng.org/c/unique-constraint/#before-and-after

## The problem

An import must store an external event once per account. Two callers both inspect the table before either inserts. They both see no row. A uniqueness rule in application code alone cannot stop both planned inserts.

## Prompt to paste

Enforce this uniqueness rule in the database. Test two concurrent inserts and return a useful conflict result to the losing caller.

## Before

The table has no constraint. Model the overlapping schedule by preparing both callers after their existence checks, then executing their inserts. Two identical account/event rows remain.

## After

Declare both key columns NOT NULL and enforce UNIQUE(account_id, event_id). Keep the preliminary check only as an optional convenience. The database rejects the second duplicate insert, including when a caller bypasses that check.

## Run the check

Save the following code as unique-constraint.mjs and run `node --experimental-sqlite --test unique-constraint.mjs` with Node.js 22.12 or later. The SQLite examples use an experimental built-in API and the shown runtime flag.

```js
import test from 'node:test';
import assert from 'node:assert/strict';
import { DatabaseSync } from 'node:sqlite';

function before() {
  const db = new DatabaseSync(':memory:');
  db.exec('CREATE TABLE events (account_id TEXT, event_id TEXT)');
  return db;
}

function prepareInsert(db, account, event) {
  const exists = db.prepare(
    'SELECT 1 FROM events WHERE account_id = ? AND event_id = ?'
  ).get(account, event);
  return () => {
    if (!exists) db.prepare('INSERT INTO events VALUES (?, ?)').run(account, event);
  };
}

function after() {
  const db = new DatabaseSync(':memory:');
  db.exec(`CREATE TABLE events (
    account_id TEXT NOT NULL,
    event_id TEXT NOT NULL,
    UNIQUE (account_id, event_id)
  )`);
  return db;
}

test('both application checks pass before either insert', () => {
  const db = before();
  try {
    const first = prepareInsert(db, 'alice', 'event-1');
    const second = prepareInsert(db, 'alice', 'event-1');
    first(); second();
    assert.equal(db.prepare('SELECT count(*) AS n FROM events').get().n, 2);
  } finally { db.close(); }
});

test('the constraint rejects duplicates and preserves account scope', () => {
  const db = after();
  try {
    const first = prepareInsert(db, 'alice', 'event-1');
    const second = prepareInsert(db, 'alice', 'event-1');
    first();
    assert.throws(second, /UNIQUE constraint failed/);
    db.prepare('INSERT INTO events VALUES (?, ?)').run('bob', 'event-1');
    assert.throws(() => db.prepare('INSERT INTO events VALUES (?, ?)').run('alice', 'event-1'),
      /UNIQUE constraint failed/);
    assert.throws(() => db.prepare('INSERT INTO events VALUES (?, ?)').run('alice', null),
      /NOT NULL constraint failed/);
    assert.equal(db.prepare('SELECT count(*) AS n FROM events').get().n, 2);
  } finally { db.close(); }
});
```

## Expected result

Without the constraint, two checks followed by two inserts store two duplicate rows. With the constraint, the second identical insert fails, while another account may use the same event ID. The check also rejects a missing event key.

## Limits

The runnable file uses real SQLite statements with an explicitly controlled overlap schedule in one connection. It does not test simultaneous database connections or another database engine. Test your deployed engine with competing connections and handle its specific conflict code. SQLite treats NULLs as distinct for UNIQUE, so required key columns need NOT NULL. Existing duplicates need reconciliation before a migration. A unique row does not by itself ensure that an external side effect ran once.

## Sources

- [SQLite: UNIQUE constraints](https://www.sqlite.org/lang_createtable.html#unique_constraints): Composite uniqueness and the treatment of NULL values.
- [SQLite: Constraint conflicts](https://www.sqlite.org/lang_conflict.html): The default ABORT behavior rejects a violating statement.
- [Node.js 22: SQLite](https://nodejs.org/docs/latest-v22.x/api/sqlite.html): The built-in DatabaseSync runtime; Node 22.12 requires the experimental SQLite flag.

Sources checked: 2026-10-11. These are original illustrative examples with automated checks. Human editorial approval is not recorded. Policy: https://vibe-coding-101.brighteng.org/editorial/