{"record":{"id":"57affca0f4ba9051","repo":"clockworklabs/SpacetimeDB","slug":"cannot-add-unique-constraint-on-table-table-id-c","errorCode":null,"errorMessage":"Cannot add unique constraint on table {table_id} column(s) {col_list:?} ({source}):\n{total} duplicate group(s) found.\n{examples}{}","messagePattern":"Cannot add unique constraint on table (.+?) column\\(s\\) (.+?) \\((.+?)\\):\n(.+?) duplicate group\\(s\\) found\\.\n(.+?)(.+?)","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"crates/datastore/src/locking_tx_datastore/mut_tx.rs","lineNumber":2418,"sourceCode":"            }\n        };\n\n        // Record whether this table had a unique index before.\n        let had_unique = commit_table.has_unique_index();\n\n        // Build a human-readable error from an index's duplicate groups. Used on both the\n        // committed-state and tx-state `make_unique` failure paths (`source` distinguishes\n        // which one fired).\n        let dup_err = |idx: &TableIndex, source: &str| {\n            let duplicates = idx.iter_duplicates();\n            let total = duplicates.len();\n            let examples: String = duplicates\n                .iter()\n                .take(10)\n                .map(|(val, count)| format!(\"  - {val:?} appears {count} times\"))\n                .collect::<Vec<_>>()\n                .join(\"\\n\");\n            anyhow::anyhow!(\n                \"Cannot add unique constraint on table {table_id} column(s) {col_list:?} \\\n                 ({source}):\\n{total} duplicate group(s) found.\\n{examples}{}\",\n                if total > 10 { \"\\n  ... and more\" } else { \"\" }\n            )\n        };\n\n        // Try to make each matching index unique on both tables. `make_unique` fails fast on\n        // the first duplicate; only if it fails do we run `iter_duplicates` to build a\n        // human-readable error (showing up to 10 duplicate groups).\n        for (i, &index_id) in index_ids.iter().enumerate() {\n            let commit_idx = commit_table.indexes.get_mut(&index_id).expect(\"index must exist\");\n            if commit_idx.make_unique().is_err() {\n                // `make_unique` restored the failing index to non-unique on error.\n                let err = dup_err(commit_idx, \"committed state\");\n                revert(commit_table, tx_table, i);\n                return Err(err.into());\n            }\n","sourceCodeStart":2400,"sourceCodeEnd":2436,"githubUrl":"https://github.com/clockworklabs/SpacetimeDB/blob/9e0d92412ff2248f401a8ad12d535f2b5ac30912/crates/datastore/src/locking_tx_datastore/mut_tx.rs#L2400-L2436","documentation":"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.","triggerScenarios":"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.","commonSituations":"Backfilling data before adding constraints; retrofitting uniqueness onto legacy tables; duplicate idempotency keys accumulated over time.","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"],"exampleFix":"-- before: duplicates block the constraint\nSELECT email, COUNT(*) FROM t GROUP BY email HAVING COUNT(*) > 1;\nALTER TABLE t ADD UNIQUE (email); -- duplicate group(s) found\n\n-- after: keep one row per duplicate group, then apply\nDELETE FROM t WHERE row_id NOT IN (SELECT MIN(row_id) FROM t GROUP BY email);\nALTER TABLE t ADD UNIQUE (email);","handlingStrategy":"validation","validationCode":"-- Pre-check for duplicates before adding the unique constraint\nSELECT email, COUNT(*) AS n FROM t GROUP BY email HAVING COUNT(*) > 1;\n-- if any rows return, deduplicate first:\nDELETE FROM t WHERE row_id NOT IN (SELECT MIN(row_id) FROM t GROUP BY email);","typeGuard":null,"tryCatchPattern":"match tx.create_constraint(schema) {\n    Err(e) if e.to_string().contains(\"duplicate group(s) found\") => {\n        // Parse the listed duplicate groups, deduplicate committed data (or fix this tx's writes), retry\n    }\n    other => other,\n}","preventionTips":["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"],"tags":["spacetimedb","datastore","constraint","unique","duplicate-data"],"backgroundTag":"unique-constraint-violation","analyzedSha":"9e0d92412ff2248f401a8ad12d535f2b5ac30912","analyzedAt":"2026-08-20T06:08:37.179Z","contentChangedAt":"2026-08-20T06:08:37.179Z","schemaVersion":2},"datasetVersion":"2026-09-14T00:17:10.932Z"}