{"record":{"id":"d172e8f5256f2764","repo":"clockworklabs/SpacetimeDB","slug":"unique-constraint-on-table-table-id-column-s-c","errorCode":null,"errorMessage":"unique constraint on table {table_id} column(s) {col_list:?} requires at least one backing index on those columns","messagePattern":"unique constraint on table (.+?) column\\(s\\) (.+?) requires at least one backing index on those columns","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"crates/datastore/src/locking_tx_datastore/mut_tx.rs","lineNumber":2353,"sourceCode":"        let table_id = constraint.table_id;\n\n        // (a) Only unique constraints are supported at the moment. Reject anything else\n        //     up front, before writing to `st_constraint`.\n        let Some(cols) = constraint.data.unique_columns().cloned() else {\n            return Err(anyhow::anyhow!(\n                \"adding non-unique constraints is not supported (constraint on table {table_id})\"\n            )\n            .into());\n        };\n        let col_list: ColList = cols.into();\n\n        // (b) A unique constraint must be backed by at least one index on the same columns.\n        //     Check on the committed table; the tx table's index set is kept in lockstep\n        //     with the committed one by the datastore, so agreement is an invariant.\n        {\n            let (_, (commit_table, _, _)) = self.get_or_create_insert_table_mut(table_id)?;\n            if commit_table.get_indexes_by_cols(&col_list).is_empty() {\n                return Err(anyhow::anyhow!(\n                    \"unique constraint on table {table_id} column(s) {col_list:?} \\\n                     requires at least one backing index on those columns\"\n                )\n                .into());\n            }\n        }\n\n        // (c) Validation passed — insert metadata into system tables. On any failure\n        //     beyond this point, the tx rollback unwinds both the st_constraint row and\n        //     the pending schema change.\n        let (constraint_id, newly_inserted) = self.create_st_constraint(constraint)?;\n\n        // If the constraint already existed in `st_constraint`, nothing new was pushed\n        // to `pending_schema_changes`, and the backing indices are already in the\n        // correct state. Return early — in particular, do NOT overwrite\n        // `pending_schema_changes.last_mut()`, which would clobber an unrelated change.\n        if !newly_inserted {\n            return Ok(constraint_id);","sourceCodeStart":2335,"sourceCodeEnd":2371,"githubUrl":"https://github.com/clockworklabs/SpacetimeDB/blob/9e0d92412ff2248f401a8ad12d535f2b5ac30912/crates/datastore/src/locking_tx_datastore/mut_tx.rs#L2335-L2371","documentation":"A unique constraint must be backed by at least one index on exactly the constrained column set. create_constraint checks the committed table's indexes via get_indexes_by_cols(col_list); if none matches, the constraint is rejected before st_constraint is touched - uniqueness can only be enforced through an index, and the tx table's index set is kept in lockstep with the committed one.","triggerScenarios":"Adding a unique constraint on columns with no index (a SQL layer creating the constraint before CREATE INDEX), or where existing indexes cover a different column set than the constrained ColList.","commonSituations":"DDL written assuming the database auto-creates a backing index; migrations that add constraints without companion index creation.","solutions":["Create an index on the same column set first, then add the constraint","Or create the index as unique directly via create_index(schema, is_unique = true) if you do not need the named constraint object","Verify the index columns exactly match the constrained columns (set equality, not subset)"],"exampleFix":"// before: constraint without a backing index\nlet constraint_id = tx.create_constraint(ConstraintSchema {\n    table_id,\n    data: constraint_data_with_unique_cols(cols.clone()),\n    ..\n})?; // -> requires at least one backing index\n\n// after: index first, then constraint\ntx.create_index(\n    IndexSchema { table_id, index_algorithm: index_algorithm_for(cols.clone()), .. },\n    /* is_unique: */ true,\n)?;\nlet constraint_id = tx.create_constraint(ConstraintSchema {\n    table_id,\n    data: constraint_data_with_unique_cols(cols),\n    ..\n})?;","handlingStrategy":"validation","validationCode":"// Ensure a backing index exists on the exact column set before adding the constraint\nlet col_list: ColList = unique_cols.into();\nlet (_, (commit_table, _, _)) = tx.get_or_create_insert_table_mut(table_id)?;\nif commit_table.get_indexes_by_cols(&col_list).is_empty() {\n    tx.create_index(\n        IndexSchema { table_id, index_algorithm: index_algorithm_for(col_list.clone()), .. },\n        /* is_unique: */ true,\n    )?;\n}\nlet constraint_id = tx.create_constraint(schema)?;","typeGuard":null,"tryCatchPattern":"match tx.create_constraint(schema) {\n    Err(e) if e.to_string().contains(\"requires at least one backing index\") => {\n        // Create the index on the same columns first, then retry the constraint\n    }\n    other => other,\n}","preventionTips":["Pair every unique constraint with an index on the same column set in migrations","Verify column sets match exactly (set equality, not subset)","Consider creating the index as unique directly if no named constraint is needed"],"tags":["spacetimedb","datastore","constraint","index","schema"],"backgroundTag":"constraint-requires-index","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"}