Skip to main content

Constraints

Invariant support is currently limited to referential integrity and not-null constraints. Unique and check constraints are not yet supported.

Supported​

Referential integrity​

ElectricSQL maintains referential integrity of foreign key references. So you can use foreign key relationships in your data model and rely on referential integrity:

CREATE TABLE posts (
  id UUID PRIMARY KEY
);

CREATE TABLE comments (
  id UUID PRIMARY KEY

  post_id UUID REFERENCES(posts.id) ON DELETE CASCADE
);

This works even when making writes locally in an offline database. See Introduction -> Conflict-free offline -> Preserving data integrity and the Rich-CRDT post on Compensations for more information.

info

To preserve referential integrity Electric prevents updates to a table's primary keys.

caution

Electric currently does not allow adding a new foreign key column with ALTER TABLE ... ADD COLUMN to an electrified table. This limitation will be removed in a future release.

Not-null constraints​

ElectricSQL supports not-null constraints as long as the constraint is defined when creating the table or before the table is electrified.

CREATE TABLE items (
  -- Implicit non null constraint
  id UUID PRIMARY KEY

  -- Explicit non null constraint
  foo TEXT NOT NULL

  -- Can be null
  bar TEXT
)

Adding a column with a not-null constraint is supported, but not advisable until default values are implemented:

  -- Unsafe additive migration, requires the table to be empty
ALTER TABLE items
  ADD COLUMN baz TEXT NOT NULL;

  -- Safe additive migration, but default values are not yet supported
ALTER TABLE items
  ADD COLUMN fie TEXT NOT NULL DEFAULT 'some_value';

Adding a column with not-null constraints after the table is electrified is technically possible because it's an additive migration as long as the table is empty or a default value is provided.

ElectricSQL does not yet support default values and you can only consider the table empty if you can guarantee that no new rows are in-flight, pending locally on a client or will be added by a client not yet updated.

This is not a guarantee that can normally be made and without it, writes that were accepted locally with implicit null values in the new column would need to be rejected, which would violate the finality of local writes.

Constraining an existing column by adding a not-null constraint to it is not supported:

ALTER TABLE items
  -- Not additive, not supported
  ALTER COLUMN bar TEXT NOT NULL;

This type of migration is not supported since it is not additive. The same reasoning about finality of local writes applies here.

Unsupported​

Unsupported constraints must be removed from a table before electrifying it.

Check constraints​

ElectricSQL does not currently support check constraints.

Unique constraints​

ElectricSQL does not currently support unique constraints.

Work in progress

We're working to support:

  • unique constraints using Reservations
  • single column and then multi-column check constraints using validation

See Rich-CRDTs for more information.