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 in the Spicepod via the primary_key field in the acceleration configuration. Additional unique constraints are specified via the indexes field with the value unique. Data that violates these constraints will result in a conflict.
If multiple rows in the incoming data violate any constraint, the entire incoming batch of data will be dropped.
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. The column reference must be enclosed in parentheses if it is a multicolumn key.
Examples
number: Reference a constraint on the number column(hash, timestamp): Reference a constraint on the hash and timestamp columnsThe 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 If there are multiple rows in the incoming data that violate any constraint, the entire incoming batch of data will be dropped. :::
Example Spicepod:
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:
DuckDB does not support upsert for datasets with List or Map types.
Standard indexes unexpectedly act like unique indexes and block updates when 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.