How to test unique constraint

Insert a row that repeats the value and read the error. In SQLite the second ana@example.com is refused with UNIQUE constraint failed: customer.email, extended result code 2067. Then insert the same column as NULL three times. All three rows are accepted, because a UNIQUE column places no limit at all on NULL.

Why check this

A unique constraint is the part of a data model a test can prove in one statement, so prove it rather than read it. Run this when a migration adds a constraint, when a model changes its keys, and on the staging copy before a release that touches signup or import code.

The failure it prevents is an account created twice under one address. The application layer checks for an existing row, two requests arrive together, both checks pass, both inserts run, and support now has two customers who each believe they own the mailbox. A constraint in the schema closes that window because the database serialises the writes.

The second failure is quieter. A column declared unique looks like a guarantee, and for NULL it is not one. Any number of rows may carry NULL in a unique column, because SQL compares NULL to NULL as unknown rather than as equal. The hole is exactly where optional fields live: phone numbers, external ids, referral codes.

Prerequisites

• sqlite3 3.50.6, 32-bit. The binary on PATH here is the Android SDK platform-tools build at /c/Android/sdk/platform-tools/sqlite3.exe. Everything ran against SQLite, not against PostgreSQL or MySQL, and the wording of the error is engine specific.
• Node 22.23.2, whose bundled node:sqlite module links SQLite 3.51.3 and exposes the extended result code that the command line hides.
• The SQLite constraint documentation for the rule about NULL.
• A throwaway database. Build the one used below.

CREATE TABLE customer (
  id      INTEGER PRIMARY KEY,
  email   TEXT UNIQUE,
  country TEXT NOT NULL
);
CREATE TABLE subscription (
  customer_id INTEGER NOT NULL,
  plan        TEXT NOT NULL,
  channel     TEXT,
  UNIQUE (customer_id, plan, channel)
);
INSERT INTO customer (id, email, country) VALUES
  (1, 'ana@example.com', 'UA'),
  (2, 'bo@example.com',  'PL');

Save that as uniq.sql and run sqlite3 uniq.db < uniq.sql. Step 4 needs one more file, uniq-error.mjs, next to the database.

import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync('uniq.db');
try {
  db.prepare('INSERT INTO customer (id, email, country) VALUES (?, ?, ?)')
    .run(3, 'ana@example.com', 'DE');
} catch (e) {
  console.log('message:', e.message);
  console.log('errcode:', e.errcode);
}
db.close();

node:sqlite is still marked experimental, so --no-warnings keeps its notice off the output below.

Steps

1. 1

   Step 1.

   Ask the database which unique indexes the table carries.

   sqlite3 uniq.db ".mode box" "PRAGMA index_list(customer);"
   

   ┌─────┬─────────────────────────────┬────────┬────────┬─────────┐
   │ seq │            name             │ unique │ origin │ partial │
   ├─────┼─────────────────────────────┼────────┼────────┼─────────┤
   │ 0   │ sqlite_autoindex_customer_1 │ 1      │ u      │ 0       │
   └─────┴─────────────────────────────┴────────┴────────┴─────────┘

   unique is 1 and origin is u, meaning a UNIQUE clause in the table definition created this index. An origin of c would mean a CREATE UNIQUE INDEX statement, and pk the primary key.

2. 2

   Step 2.

   Read which column that index covers. The generated name does not say.

   sqlite3 uniq.db ".mode box" "PRAGMA index_info(sqlite_autoindex_customer_1);"
   

   ┌───────┬─────┬───────┐
   │ seqno │ cid │ name  │
   ├───────┼─────┼───────┤
   │ 0     │ 1   │ email │
   └───────┴─────┴───────┘

   One row, so the key is one column wide. A composite key returns one row per column, in key order.

3. 3

   Step 3.

   Insert a value that already exists and read the refusal.

   sqlite3 uniq.db "INSERT INTO customer (id, email, country) VALUES (3, 'ana@example.com', 'DE');"
   

   Error: stepping, UNIQUE constraint failed: customer.email (19)

   The process exits with status 19. That is SQLITE_CONSTRAINT, the primary result code, shared by every constraint in the engine.

4. 4

   Step 4.

   Repeat the same insert through node:sqlite to get the code an application can branch on.

   node --no-warnings uniq-error.mjs
   

   message: UNIQUE constraint failed: customer.email
   errcode: 2067

   2067 is SQLITE_CONSTRAINT_UNIQUE. A handler that maps 19 to "duplicate email" also maps NOT NULL and CHECK failures to it, and returns the wrong message to the user.

5. 5

   Step 5.

   Insert the unique column as NULL, three times, in one statement.

   sqlite3 uniq.db ".mode box" "INSERT INTO customer (id, email, country) VALUES (4, NULL, 'UA'), (5, NULL, 'PL'), (6, NULL, 'DE'); SELECT count(*) AS all_rows, count(email) AS with_email, count(DISTINCT email) AS distinct_email FROM customer;"
   

   ┌──────────┬────────────┬────────────────┐
   │ all_rows │ with_email │ distinct_email │
   ├──────────┼────────────┼────────────────┤
   │ 5        │ 2          │ 2              │
   └──────────┴────────────┴────────────────┘

   All three went in. count(*) counts rows, count(email) skips NULL, and the gap between them is how many rows the unique constraint never examined.

6. 6

   Step 6.

   Do the same to a composite key where one member is nullable.

   sqlite3 uniq.db ".mode box" "INSERT INTO subscription (customer_id, plan, channel) VALUES (1,'pro',NULL),(1,'pro',NULL),(1,'pro',NULL); SELECT customer_id, plan, channel, count(*) AS n FROM subscription GROUP BY customer_id, plan;"
   

   ┌─────────────┬──────┬─────────┬───┐
   │ customer_id │ plan │ channel │ n │
   ├─────────────┼──────┼─────────┼───┤
   │ 1           │ pro  │         │ 3 │
   └─────────────┴──────┴─────────┴───┘

   One customer now holds the same plan three times. A NULL in any member of a composite key makes that whole row invisible to the constraint.

7. 7

   Step 7.

   Try to add the key the model actually needed, and watch the rows from step 6 block it.

   sqlite3 uniq.db "CREATE UNIQUE INDEX subscription_one_plan ON subscription(customer_id, plan);"
   

   Error: stepping, UNIQUE constraint failed: subscription.customer_id, subscription.plan (19)

   This is the migration failing in production at 02:00. Find the blocking rows first with a grouped count, which is the subject of the duplicate rows procedure below.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | unique is 1, origin is u | The constraint is declared in the table definition | Confirm the column with PRAGMA index_info. | | No row from index_list | Nothing enforces uniqueness on that table | Treat every application-side duplicate check as a race, not a guarantee. | | UNIQUE constraint failed: table.column | The constraint is live and names the column | Assert on this in the test, not on a generic error. | | Extended code 2067 | A uniqueness failure specifically | Map it to the duplicate message. Map 1299 and 275 elsewhere. | | count(*) far above count(column) | Many NULL rows sit outside the constraint | Add NOT NULL, or a partial index, before trusting the column as a key. | | CREATE UNIQUE INDEX fails | Existing rows already violate the new key | Run the grouped count, clean the data, then retry the migration. |

Common mistakes

What to check next

• How to check duplicate rows in sql: finds the rows that will block the index before the migration does.
• How to check foreign key: the other declared constraint that may not be enforcing anything.
• How to check if a table has an index: reads the same pragmas for performance rather than for correctness.
• How to check database integrity: what the engine verifies about its own indexes, and what it never looks at.

FAQ

Does a unique constraint stop duplicate NULLs?

No. SQL compares NULL to NULL as unknown, so a unique index never sees two NULL rows as equal. Three NULL emails went into a UNIQUE column above. Add NOT NULL if the column is a key, or accept that optional columns are not keys.

Is UNIQUE the same as a unique index?

In SQLite a UNIQUE clause creates an index named sqlite_autoindex_<table>_<n>, which PRAGMA index_list reports with origin of u. A hand-written CREATE UNIQUE INDEX reports c. Enforcement is identical. Only the name and the origin differ.

How do I find the rows blocking a unique index?

Group by the columns of the intended key and keep the groups with more than one row. That query returns the exact keys to clean, and it runs before the migration rather than during it.

Should the test assert on the error message or the code?

Assert on the extended result code, 2067 here, and treat the message as documentation. The message names the table and column, which is useful in a failure report, but its wording is engine specific and changes between releases.

Verified