unique constraint on table {table_id} column(s) {col_list:?}

Error message

unique constraint on table {table_id} column(s) {col_list:?} requires at least one backing index on those columns

What it means

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.

Source

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

        let table_id = constraint.table_id;

        // (a) Only unique constraints are supported at the moment. Reject anything else
        //     up front, before writing to `st_constraint`.
        let Some(cols) = constraint.data.unique_columns().cloned() else {
            return Err(anyhow::anyhow!(
                "adding non-unique constraints is not supported (constraint on table {table_id})"
            )
            .into());
        };
        let col_list: ColList = cols.into();

        // (b) A unique constraint must be backed by at least one index on the same columns.
        //     Check on the committed table; the tx table's index set is kept in lockstep
        //     with the committed one by the datastore, so agreement is an invariant.
        {
            let (_, (commit_table, _, _)) = self.get_or_create_insert_table_mut(table_id)?;
            if commit_table.get_indexes_by_cols(&col_list).is_empty() {
                return Err(anyhow::anyhow!(
                    "unique constraint on table {table_id} column(s) {col_list:?} \
                     requires at least one backing index on those columns"
                )
                .into());
            }
        }

        // (c) Validation passed — insert metadata into system tables. On any failure
        //     beyond this point, the tx rollback unwinds both the st_constraint row and
        //     the pending schema change.
        let (constraint_id, newly_inserted) = self.create_st_constraint(constraint)?;

        // If the constraint already existed in `st_constraint`, nothing new was pushed
        // to `pending_schema_changes`, and the backing indices are already in the
        // correct state. Return early — in particular, do NOT overwrite
        // `pending_schema_changes.last_mut()`, which would clobber an unrelated change.
        if !newly_inserted {
            return Ok(constraint_id);

View on GitHub (pinned to 9e0d92412f)

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)

Example fix

// before: constraint without a backing index
let constraint_id = tx.create_constraint(ConstraintSchema {
    table_id,
    data: constraint_data_with_unique_cols(cols.clone()),
    ..
})?; // -> requires at least one backing index

// after: index first, then constraint
tx.create_index(
    IndexSchema { table_id, index_algorithm: index_algorithm_for(cols.clone()), .. },
    /* is_unique: */ true,
)?;
let constraint_id = tx.create_constraint(ConstraintSchema {
    table_id,
    data: constraint_data_with_unique_cols(cols),
    ..
})?;

Defensive patterns

Strategy: validation

Validate before calling

// Ensure a backing index exists on the exact column set before adding the constraint
let col_list: ColList = unique_cols.into();
let (_, (commit_table, _, _)) = tx.get_or_create_insert_table_mut(table_id)?;
if commit_table.get_indexes_by_cols(&col_list).is_empty() {
    tx.create_index(
        IndexSchema { table_id, index_algorithm: index_algorithm_for(col_list.clone()), .. },
        /* is_unique: */ true,
    )?;
}
let constraint_id = tx.create_constraint(schema)?;

Try / catch

match tx.create_constraint(schema) {
    Err(e) if e.to_string().contains("requires at least one backing index") => {
        // Create the index on the same columns first, then retry the constraint
    }
    other => other,
}

Prevention

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

When it happens

Trigger: 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.

Common situations: DDL written assuming the database auto-creates a backing index; migrations that add constraints without companion index creation.

Related errors

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