Skip to main content

ADD CONSTRAINT clause

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Adds an informational primary key, informational foreign key, or an enforced check constraint to an existing Delta Lake table.

Syntax​

ADD [check_constraint | key_constraint ]

check_constraint
    CONSTRAINT name CHECK ( condition )

key_constraint
    { [ CONSTRAINT name ]
      {
        PRIMARY KEY ( key_column [ TIMESERIES ] [, ...] ) [ constraint_option [...] ] |
        { FOREIGN KEY (foreign_key_column [, ...] )
          REFERENCES parent_relation [ ( parent_column [, ...] ] )
          [ constraint_option | foreign_key_option ] [...]
      }
    }

constraint_option
    { NOT ENFORCED |
      DEFERRABLE |
      INITIALLY DEFERRED |
      { NORELY | RELY } }

foreign_key_option
    { MATCH FULL |
      ON UPDATE NO ACTION |
      ON DELETE NO ACTION }

For compatibility with non-standard SQL dialects you can specify ENABLE NOVALIDATE instead of NOT ENFORCED DEFERRABLE INITIALLY DEFERRED.

Parameters​

  • check_constraint

    Defines a check constraint for a relation.

    • CONSTRAINT name

      Specifies a name for the constraint. The name must be unique within the relation.

    • CHECK ( condition )

      The DEFAULT COLLATION of the relation must be UTF8_BINARY at the time the constraint is added.

      condition must be a deterministic expression returning a BOOLEAN.

      condition may be composed of literals, column identifiers within the relation, and deterministic, built-in SQL functions or operators except:

      Also condition must not contain any subquery.

      For a CHECK constraint to be satisfied in Databricks it must evaluate to true.

      Delta Lake verifies the validity of the check constraint against both new and existing data. If any existing row violates the constraint an error will be raised.

  • key_constraint

    Preview

    This feature is in Public Preview.

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

    Defines an informational primary key or informational foreign key constraint for a relation. Informational key constraints are not enforced but can improve performance by supporting query optimizations.

    • CONSTRAINT name

      Optionally specifies a name for the constraint. The name must be unique within the schema. If no name is provided Databricks will generate one.

    • PRIMARY KEY ( key_column [ TIMESERIES ] [, ...] ) [ constraint_option [...] ]

      Applies to: check marked yes Unity Catalog only

      Adds a primary key constraint to the relation. A relation can have at most one primary key.

      Primary key constraints are not supported for relations in the hive_metastore catalog.

    • key_column

      A column of the subject relation defined as NOT NULL. Column names must not be repeated.

    • TIMESERIES

      Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above

      Optionally labels the primary key column component as representing a timeseries.

    • FOREIGN KEY ( foreign_key_column [, ...] ) REFERENCES parent_relation [ ( parent_column [, ...] ) ] foreign_key_option

      Applies to: check marked yes Unity Catalog only

      Adds a foreign key (referential integrity) constraint to the relation.

      Foreign key constraints are not supported for relations in the hive_metastore catalog.

      • foreign_key_column

        A column of the subject relation. Column names must not be repeated. The data type of each column must match the type of the matching parent_column. The number of columns must match the number of parent_columns. Two foreign keys cannot share an identical set of foreign key columns.

      • parent_relation

        Specifies the relation the foreign key refers to. The relation must have a defined PRIMARY KEY constraint, and you must have the SELECT privilege on the relation.

      • parent_column

        A column in the parent relation which is part of its primary key. All primary key columns of the parent relation must be listed.

        If parent columns are not listed they are implied to be specified in the order given in the PRIMARY KEY definition.

      Foreign key constraints which only differ in the permutation of the foreign key columns are not allowed.

    • constraint_option

      Lists the properties of the constraints. All properties are optional but implied by default. Each property can at most be specified once.

      • NOT ENFORCED

        Databricks takes no action to enforce it for existing or new rows.

      • DEFERRABLE

        The constraint enforcement can be deferred.

      • INITIALLY DEFERRED

        Constraint enforcement is deferred.

      • NORELY or RELY

        Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 14.2 and above for PRIMARY KEY constraints

        Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 15.4 and above for FOREIGN KEY constraints

        If RELY, Databricks may exploit the constraint to rewrite queries. It is the user's responsibility to ensure the constraint is satisfied. Relying on a constraint that is not satisfied may lead to incorrect query results.

        The default is NORELY.

    • foreign_key_option

      Lists the properties specific to foreign key constraints. All properties are optional but implied by default. Each property can at most be specified once.

      • MATCH FULL

        For the constraint to be considered true all column values must be NOT NULL.

      • ON UPDATE NO ACTION

        If the parent PRIMARY KEY is updated Databricks takes no action to restrict the update or update the foreign key.

      • ON DELETE NO ACTION

        If the parent row is deleted Databricks takes no action to restrict the action, update the foreign key, or delete the dependent row.

important

Databricks does not enforce primary key or foreign key constraints. Confirm key constraints before adding a primary or foreign key. Your ingest process may provide such assurance, or you can run checks against your data.

Examples​

SQL
-- Add a primary key
> CREATE TABLE persons(first_name STRING NOT NULL, last_name STRING NOT NULL, nickname STRING);
> ALTER TABLE persons ADD CONSTRAINT persons_pk PRIMARY KEY(first_name, last_name);

-- Add a foreign key which Databricks does not enforce, but can rely upon.
> CREATE TABLE pets(name STRING, owner_first_name STRING, owner_last_name STRING);
> ALTER TABLE pets ADD CONSTRAINT pets_persons_fk
    FOREIGN KEY(owner_first_name, owner_last_name) REFERENCES persons
    NOT ENFORCED RELY;

-- Add a check contraint
> ALTER TABLE pets ADD CONSTRAINT pets_name_not_cute_chk CHECK (length(name) < 20);
Last updated on