Constraints

Delta tables support standard SQL constraint management clauses that ensure that the quality and integrity of data added to a table is automatically verified. When a constraint is violated, Delta Lake throws an InvariantViolationException to signal that the new data can’t be added.

Two types of constraints are supported:

  • NOT NULL: indicates that values in specific columns cannot be null.
  • CHECK: indicates that a specified Boolean expression must be true for each input row.

NOT NULL constraint

Note

  • DROP NOT NULL is available in Databricks Runtime 7.0 and above.
  • SET NOT NULL is available in Databricks Runtime 7.4 and above.
  • Before adding a NOT NULL constraint to a table, Databricks verifies that all existing rows satisfy the constraint.

You specify NOT NULL constraints in the schema when you create a table and drop NOT NULL constraints using the ALTER TABLE CHANGE COLUMN command.

CREATE TABLE events(
  id LONG NOT NULL,
  date STRING NOT NULL,
  location STRING,
  description STRING
) USING DELTA;

ALTER TABLE events CHANGE COLUMN date DROP NOT NULL;

You can add NOT NULL constraints to an existing Delta table using the ALTER TABLE CHANGE COLUMN SET NOT NULL command.

CREATE TABLE events(
  id LONG,
  date STRING,
  location STRING,
  description STRING
) USING DELTA;

ALTER TABLE events CHANGE COLUMN id SET NOT NULL;

If you specify a NOT NULL constraint on a column nested within a struct, the parent struct is also constrained to not be null. However, columns nested within array or map types do not accept NOT NULL constraints.

For reference information, see CREATE TABLE USING and ALTER TABLE CHANGE COLUMN.

CHECK constraint

Note

  • Available in Databricks Runtime 7.4 and above.
  • In Databricks Runtime 7.3 LTS you can write to tables with CHECK constraints defined but you cannot create CHECK constraints.

You manage CHECK constraints using the ALTER TABLE ADD CONSTRAINT and ALTER TABLE DROP CONSTRAINT commands. ALTER TABLE ADD CONSTRAINT verifies that all existing rows satisfy the constraint before adding it to the table.

CREATE TABLE events(
  id LONG NOT NULL,
  date STRING,
  location STRING,
  description STRING
) USING DELTA;

ALTER TABLE events ADD CONSTRAINT dateWithinRange CHECK (date > '1900-01-01');
ALTER TABLE events DROP CONSTRAINT dateWithinRange;

For reference information, see ALTER TABLE ADD CONSTRAINT and ALTER TABLE DROP CONSTRAINT.

CHECK constraints are table properties in the output of the DESCRIBE DETAIL and SHOW TBLPROPERTIES commands.

ALTER TABLE events ADD CONSTRAINT validIds CHECK (id > 1000 and id < 999999);
DESCRIBE DETAIL events;
Displayed constraint