unique constraint

The database rejects duplicate values for a declared key.

Use this when Two simultaneous requests can create the same logical record.

without the term

“If someone double-clicks sign up, we sometimes get two accounts with the same email, even though the code checks if the email exists first. Can you make duplicates impossible?”

with the term · How do you ask a model for unique constraint?

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

X Bluesky LinkedIn

What is unique constraint?

A unique constraint enforces a key’s uniqueness across writers, including writers outside the application.

Why does unique constraint matter when vibe coding?

Checking for an existing row before inserting leaves a race window.

How do you do unique constraint?

Choose the true business key, including tenant scope when needed. Check simultaneous conflicting inserts.

What goes wrong with unique constraint?

The constraint omits tenant scope or conflicts with existing duplicate data.

Before and after

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.

Shared import for both versions

import { DatabaseSync } from 'node:sqlite';

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.

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);
  };
}

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.

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;
}

Check the change

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.

Save the runnable comparison, then run node --experimental-sqlite --test unique-constraint.mjs with Node.js 22.12 or later. This uses Node’s experimental SQLite API and the runtime flag shown above.

Download the complete teaching example: the bug, prompt, runnable code, expected result, limits, and sources, linked back to this page.

Show the assertions

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(); }
});

When this is not enough

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.

The next decision

idempotency: Decide how a repeated operation obtains its result and handles payload mismatches.
transaction: Keep the deduplication record and related local changes in the same commit unit where appropriate.

Sources and verification

SQLite: UNIQUE constraints
Composite uniqueness and the treatment of NULL values.
SQLite: Constraint conflicts
The default ABORT behavior rejects a violating statement.
Node.js 22: SQLite
The built-in DatabaseSync runtime; Node 22.12 requires the experimental SQLite flag.

Sources checked 2026-10-11. These are illustrative examples with automated checks. How we check content.

Did this help with your task?

After trying the prompt or check, tell us what happened. This optional response records only your choice and this term.