What causes "ERROR: Field %s.%s has unique constraint - cannot have default value" in clockworklabs/SpacetimeDB?

Drop either the unique constraint or the default on that column — decide which behavior is intended

ERROR: Field %s.%s has unique constraint - cannot have defau

Error message

ERROR: Field %s.%s has unique constraint - cannot have default value

What it means

AddColumnDefault rejects a default on a column that carries a single-column unique constraint: scanning table->constraints for the unique variant (tag 0) whose columns == {field_idx} finds a clash, prints this message (no trailing newline in this build) and returns, dropping the default. Unique columns derive their distinctness guarantee from enforcement at insert time, and a fixed default would either violate uniqueness on the second row or misrepresent it.

Source

Thrown at crates/bindings-cpp/include/spacetimedb/internal/v9_builder.h:657

    // Validate: default values cannot be used with primary_key, unique, or auto_inc
    // Check if this column is in the primary key
    for (uint16_t pk_col : table->primary_key) {
        if (pk_col == field_idx) {
            std::string error_msg = "ERROR: Field " + table_name + "." + field_name + 
                        " has primary_key constraint - cannot have default value";
            fprintf(stderr, "%s", error_msg.c_str());
            // fprintf(stderr, "ERROR:  has primary_key constraint - cannot have default value",
            //          table_name.c_str(), field_name.c_str());
            return;
        }
    }
    
    // Check if this column has a unique constraint
    for (const auto& constraint : table->constraints) {
        if (constraint.data.get_tag() == 0) {  // Unique constraint variant
            const auto& unique_data = constraint.data.get<0>();
            if (unique_data.columns.size() == 1 && unique_data.columns[0] == field_idx) {
                fprintf(stderr, "ERROR: Field %s.%s has unique constraint - cannot have default value",
                        table_name.c_str(), field_name.c_str());
                return;
            }
        }
    }
    
    // Check if this column has an auto_inc sequence
    for (const auto& sequence : table->sequences) {
        if (sequence.column == field_idx) {
            fprintf(stderr, "ERROR: Field %s.%s has auto_inc constraint - cannot have default value",
                    table_name.c_str(), field_name.c_str());
            return;
        }
    }
    
    // Create the column default value structure
    RawColumnDefaultValueV9 col_default;
    col_default.table = table_name;

View on GitHub (pinned to 524b4487d9)

Solutions

Drop either the unique constraint or the default on that column — decide which behavior is intended
If unique values with a fallback are needed, compute them in the reducer (e.g. derive a unique suffix) instead of a static column default
Rebuild and verify the conflict message is gone and the schema matches intent

Example fix

// before
ST_UNIQUE(account, email);
ST_COLUMN_DEFAULT(account, email, "unknown@invalid");   // unique + default
// after
ST_UNIQUE(account, email);
// (default removed — reducer assigns email when not supplied)

Defensive patterns

Strategy: validation

Validate before calling

// Policy check: refuse defaults on single-column unique constraints
for (const auto& c : table.constraints)
    if (c.data.get_tag() == 0 && c.data.get<0>().columns.size() == 1
        && c.data.get<0>().columns[0] == field_idx) /* forbid the default */;

Prevention

When adding a unique constraint, audit the same column for an existing default (and vice versa)
Generate unique fallback values in reducers, not static defaults
Keep a lint/test that rejects unique+default pairs module-wide

When it happens

Trigger: A column annotated both unique (ST_UNIQUE) and with a column default; the unique constraint was registered first so table->constraints already contains it when AddColumnDefault runs for the same field index.

Common situations: Defaulting a username/email/token column that is also marked unique; adding unique to an existing defaulted column during a hardening pass; migrations that add defaults before checking existing constraints.

Related errors

AI-assisted analysis of clockworklabs/SpacetimeDB@524b4487d9 (2026-08-16). Data as JSON: /api/errors/07c96eaed22aeb05. Report an issue: GitHub.