Constraints enforce data integrity in a database. Spice supports constraints on locally accelerated tables to ensure data quality and configure behavior for data updates that violate constraints.
Constraints are specified using column references (opens in a new tab) in the Spicepod via the primary_key field in the acceleration configuration. Additional unique constraints are specified via the indexes (opens in a new tab) field with the value unique. Data that violates these constraints will result in a conflict (opens in a new tab).
Spice Cayenne keeps one row per primary key (opens in a new tab). On other accelerators, what happens to a violating row depends on the engine and the on_conflict setting, as described in Handling conflicts (opens in a new tab).
Example Spicepod:
Column references can be used to specify which columns are part of the constraint. The column reference can be a single column name or a multicolumn key. A multicolumn key is a comma-separated list of column names, and the enclosing parentheses are optional.
Examples
number: Reference a constraint on the number column(hash, timestamp): Reference a constraint on the hash and timestamp columnshash, timestamp: The same multicolumn key, written without parenthesesThe names in a column reference are matched against the schema's field names as written — they are not SQL identifiers, so a name that SQL would read as a qualifier chain (service.instance.id) or reject outright (sentry-environment, 2xx_count) is a single column name here and needs no special treatment.
A name may also be double-quoted the way SQL writes it. The quotes are not part of the name, and any whitespace or casing inside them is preserved:
service.instance.id and "service.instance.id" both reference the same column(time_unix_nano, "service.instance.id"): A multicolumn key mixing both formsA column whose name contains ,, ;, :, (, ) or " cannot be referenced. Each of those characters separates fields in the strings a column reference is carried in, so such a name cannot be read back unambiguously; the runtime refuses it at load with a configuration error naming the column and the character, rather than silently splitting it.
Every column named by primary_key must be populated in the incoming data. On the Spice Cayenne (opens in a new tab) accelerator a batch carrying a null in any primary key column is rejected with an error naming the offending column(s), for example:
Either populate the column in the source data, or choose a primary_key made only of columns that are always present.
On the Spice Cayenne (opens in a new tab) accelerator, a primary_key alone decides which version of a key is kept, with nothing else to configure. Cayenne is the default engine (opens in a new tab) on Linux and macOS, so this also applies to an acceleration that sets no engine there. A refresh keeps:
| Dataset | Version kept for a repeated key |
|---|---|
primary_key and a time_column that is not part of the primary_key, with refresh_mode: full or append | The newest by time_column. On equal times, the version that arrived last. A NULL time is older than any time. |
refresh_mode: changes | Each source change, applied in order. |
Any other dataset with a primary_key, such as one with no time_column or with the time_column in its primary_key | The version that arrived last. |
The rule applies to a key repeated within one write and to a key the table already stores. A SQL INSERT keeps the version that arrived last. Arrival order is not defined when a source is read in parallel, so set a time_column (opens in a new tab) for a reproducible result. With refresh_mode: append, set refresh_append_overlap (opens in a new tab) to how late rows can arrive, so that a late update to an older row is re-read. Each Cayenne dataset with a primary_key logs its rule at load, for example:
A refresh that orders versions by time_column fails when the rows it reads do not include that column. An UPDATE that would give a row a key another row keeps, or a NULL key, fails and changes nothing. See Duplicate primary keys in one write (opens in a new tab) for the full rules, the messages Cayenne logs, and the dataset_acceleration_rows_superseded metric.
Two configurations do not keep one row per key. cayenne_pk_conflict_detection: none (opens in a new tab) turns off key resolution, so repeated keys are stored as written; registration refuses it on datasets that rewrite stored keys, such as refresh_mode: changes and durable write-back. A partitioned acceleration (partition_by) resolves keys within each partition, so a key that lands in two partitions is stored once in each unless every partition column is part of the primary_key.
:::warning Deprecated
acceleration.on_conflict is deprecated and will be removed in Spice 3.0. Spice Cayenne does not use it to resolve keys: it keeps one row per primary key (opens in a new tab), with the exceptions listed there, and logs a warning at load that names any change in which version is kept. Other accelerators keep the behavior described in this section and log a warning at load:
When the dataset's refresh mode resolves to changes and its acceleration uses mode: memory (the default) or mode: file_create, the warning ends with one more sentence:
Some datasets still require an on_conflict upsert on the primary key, so keep the entry on them even though they log the warning: PostgreSQL (opens in a new tab), MySQL (opens in a new tab), and MongoDB (opens in a new tab) change streams on engines other than Spice Cayenne and Arrow. Change streams and durable write-back (opens in a new tab) on Spice Cayenne need only primary_key. on_conflict no longer decides where a read-write dataset's writes go; set acceleration.write_mode: acceleration (opens in a new tab) to keep writes in the acceleration.
:::
On accelerators other than Spice Cayenne, the behavior of inserting data that violates the constraint can be configured via the on_conflict field to either drop the data that violates the constraint or upsert that data into the accelerated table (i.e. update all values other than the columns that are part of the constraint to match the incoming data).
:::warning A key can repeat within the incoming data itself, not only against a stored row.
drop on DuckDB and SQLite: the accelerator keeps the first copy of each key in arrival order and drops the later copies, then resolves the remaining rows against the stored rows. With more than one drop target, this applies to full refreshes only, and an append reaches the engine unchanged. Other accelerators do not apply this rule.
upsert: a key repeated within one record batch fails the write unless upsert_dedup_by_row_id is set, or every copy of the key is an exact duplicate and upsert_dedup is set (see advanced upsert options (opens in a new tab)). On DuckDB, a key that a full refresh repeats across record batches does not fail the write, and which copy is kept can vary from run to run.
Spice Cayenne does not use on_conflict to resolve keys and keeps one row per primary key (opens in a new tab).
:::
Example Spicepod:
By default, even when upsert is configured, if there are constraint violations, such as duplicates within the same batch of ingested data, it will result in a constraint violation - as attempting to upsert data into the target acceleration engine results in an error if done in a single statement. (i.e. PostgreSQL rejects an INSERT that proposes the same row more than once (opens in a new tab)) Spice Cayenne does not use these options; it keeps one row per primary key (opens in a new tab).
Spice provides two upsert options to resolve duplicates within a single update:
upsert_dedup: Removes exact duplicates in the incoming batch if there is a constraint violation. (i.e. the equivalent of running SELECT DISTINCT * FROM [batch])upsert_dedup_by_row_id: Resolves conflicts by taking the row with the greatest row id. This is the behavior that would occur if the upsert were applied row-by-row. This guarantees that no constraint violations would result in an error, but it has the tradeoff of being effectively "random" if the incoming data is not ordered.Neither option is ordered by time_column (opens in a new tab). upsert_dedup drops only exact duplicates (every column equal). If the same primary key appears twice in one batch with different payloads, the load errors rather than picking one revision.
upsert_dedup_by_row_id is last-write-wins by the order rows land in that batch, not by an update timestamp. Parallel scan or insert can reorder rows across partitions. An ORDER BY in refresh_sql (opens in a new tab) is not a guarantee through to conflict resolution.
Safer pattern: collapse to latest-per-key first (upstream, or by filtering in refresh_sql so a batch cannot carry two revisions of the same key), then upsert.
The new behavior is only triggered when an incoming batch has a constraint violation, minimizing the effect of applying these computations to only when its necessary. However, they can have a performance impact and are not enabled by default.
Full configuration example:
Take these two CSV files:
`one.csv`:
```csv
foo,bar
a,1
b,2
a,1
```
Behavior on `one.csv` with a primary key on `foo` and `on_conflict` set to:
- `upsert`: Will error with: `Constraint Violation: Incoming data violates uniqueness constraint on column(s): foo`
- `upsert_dedup`: Will succeed in loading 2 rows, the `a,1` row is reduced to a single instance.
- `upsert_dedup_by_row_id`: Same as `upsert_dedup`
`two.csv`:
```csv
foo,bar
a,1
b,2
a,10
```
Behavior on `one.csv` with a primary key on `foo` and `on_conflict` set to:
- `upsert`: Will error with: `Constraint Violation: Incoming data violates uniqueness constraint on column(s): foo`
- `upsert_dedup`: Will error with: `Constraint Violation: Incoming data violates uniqueness constraint on column(s): foo`
- `upsert_dedup_by_row_id`: Will succeed in loading 2 rows, `a,10` and `b,2`. The primary key violation is resolved to the row that occurred later.
</div>
</details>
Single on_conflict target supported: Only a single on_conflict target can be specified, unless all on_conflict targets are specified with drop.
:::danger[Invalid]
:::
The following Spicepod is valid because it specifies multiple `on_conflict` targets with `drop`, which is allowed:
:::tip[Valid]
:::
The following Spicepod is invalid because it specifies multiple `on_conflict` targets with `upsert` and `drop`:
:::danger[Invalid]
:::
DuckDB Limitations:
upsert for datasets with List or Map types.upsert is configured.
```yaml
datasets:
- from: spice.ai/eth.recent_blocks
name: eth.recent_blocks
acceleration:
enabled: true
engine: duckdb
primary_key: hash
indexes:
number: enabled
on_conflict:
hash: upsert
```
The following error is returned when attempting to upsert data into the `eth.recent_blocks` table:
```bash
ERROR runtime::accelerated_table::refresh: Error adding data for eth.recent_blocks: External error:
Unable to insert into duckdb table: Binder Error: Can not assign to column 'number' because
it has a UNIQUE/PRIMARY KEY constraint
```
This is a limitation of DuckDB.