Cannot add unique constraint on table {table_id} column(s) {

Error message

Cannot add unique constraint on table {table_id} column(s) {col_list:?} ({source}):
{total} duplicate group(s) found.
{examples}{}

What it means

Adding a unique constraint failed because existing rows already contain duplicate values in the constrained columns. The datastore calls make_unique on each matching index (committed state first, then tx state); on failure it iterates duplicate groups and builds a report with the total count, up to 10 examples (value and occurrence count), and a truncation marker. The source field tells you whether duplicates live in committed data or were introduced earlier in the same transaction.

Source

Thrown at crates/datastore/src/locking_tx_datastore/mut_tx.rs:2418

            }
        };

        // Record whether this table had a unique index before.
        let had_unique = commit_table.has_unique_index();

        // Build a human-readable error from an index's duplicate groups. Used on both the
        // committed-state and tx-state `make_unique` failure paths (`source` distinguishes
        // which one fired).
        let dup_err = |idx: &TableIndex, source: &str| {
            let duplicates = idx.iter_duplicates();
            let total = duplicates.len();
            let examples: String = duplicates
                .iter()
                .take(10)
                .map(|(val, count)| format!("  - {val:?} appears {count} times"))
                .collect::<Vec<_>>()
                .join("\n");
            anyhow::anyhow!(
                "Cannot add unique constraint on table {table_id} column(s) {col_list:?} \
                 ({source}):\n{total} duplicate group(s) found.\n{examples}{}",
                if total > 10 { "\n  ... and more" } else { "" }
            )
        };

        // Try to make each matching index unique on both tables. `make_unique` fails fast on
        // the first duplicate; only if it fails do we run `iter_duplicates` to build a
        // human-readable error (showing up to 10 duplicate groups).
        for (i, &index_id) in index_ids.iter().enumerate() {
            let commit_idx = commit_table.indexes.get_mut(&index_id).expect("index must exist");
            if commit_idx.make_unique().is_err() {
                // `make_unique` restored the failing index to non-unique on error.
                let err = dup_err(commit_idx, "committed state");
                revert(commit_table, tx_table, i);
                return Err(err.into());
            }

View on GitHub (pinned to 9e0d92412f)

Solutions

Inspect the duplicate groups listed in the error and delete or merge all but one row per group, then retry
If the source indicates tx-state duplicates, fix the writes earlier in the same transaction instead of touching committed data
Run a duplicate pre-check (GROUP BY ... HAVING count > 1) during a maintenance window before applying the constraint

Example fix

-- before: duplicates block the constraint
SELECT email, COUNT(*) FROM t GROUP BY email HAVING COUNT(*) > 1;
ALTER TABLE t ADD UNIQUE (email); -- duplicate group(s) found

-- after: keep one row per duplicate group, then apply
DELETE FROM t WHERE row_id NOT IN (SELECT MIN(row_id) FROM t GROUP BY email);
ALTER TABLE t ADD UNIQUE (email);

Defensive patterns

Strategy: validation

Validate before calling

-- Pre-check for duplicates before adding the unique constraint
SELECT email, COUNT(*) AS n FROM t GROUP BY email HAVING COUNT(*) > 1;
-- if any rows return, deduplicate first:
DELETE FROM t WHERE row_id NOT IN (SELECT MIN(row_id) FROM t GROUP BY email);

Try / catch

match tx.create_constraint(schema) {
    Err(e) if e.to_string().contains("duplicate group(s) found") => {
        // Parse the listed duplicate groups, deduplicate committed data (or fix this tx's writes), retry
    }
    other => other,
}

Prevention

Enforce uniqueness from the first schema revision instead of retrofitting
Run duplicate pre-checks (GROUP BY / HAVING) before constraint migrations
Make backfill jobs idempotent so they cannot introduce duplicate keys

When it happens

Trigger: Running create_constraint / a unique-constraint DDL on a table whose committed rows contain repeated values in the constrained column set, or where rows inserted earlier in the same transaction create duplicates.

Common situations: Backfilling data before adding constraints; retrofitting uniqueness onto legacy tables; duplicate idempotency keys accumulated over time.

Related errors

AI-assisted analysis of clockworklabs/SpacetimeDB@9e0d92412f (2026-08-20). Data as JSON: /api/errors/57affca0f4ba9051. Report an issue: GitHub.