Why does DEFINE INDEX UNIQUE fail on my SurrealDB table?

UNIQUE index on an existing table

Warning: the statement succeeds, then writes fail, access changes, or the table is held up.

What happens

Observed on SurrealDB 3.0.2: with two records sharing email = 'a@x', DEFINE INDEX user_email ON user FIELDS email UNIQUE fails with "Database index `user_email` already contains 'a@x'".

Why it is dangerous on a populated table

SurrealDB checks a schema change against new writes, not the records already stored, so an empty table hides it: on a populated one every old record is a write waiting to fail, and whole-table work runs for the length of the table in one transaction.

Fires on

DEFINE INDEX user_email ON TABLE user FIELDS email UNIQUE;

The safe pattern

-- run first, resolve what it returns:
--   SELECT email, count() AS n FROM user GROUP BY email;
-- then, in a later migration:
--   DEFINE INDEX user_email ON TABLE user FIELDS email UNIQUE CONCURRENTLY;

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

DEFINE INDEX user_email ON TABLE user FIELDS email UNIQUE CONCURRENTLY;

DEFINE INDEX user_email ON TABLE user FIELDS email UNIQUE;

DEFINE TABLE user SCHEMAFULL;
DEFINE INDEX user_email ON TABLE user FIELDS email UNIQUE;

DEFINE INDEX user_email ON TABLE user FIELDS email CONCURRENTLY;

How to check locally

npx bolvrk check migration.surql --engine=surrealdb