ADD CONSTRAINT clause
Applies to: Databricks SQL
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 ) [ ENFORCED ]
key_constraint
{ [ CONSTRAINT name ]
{
PRIMARY KEY ( key_column [ TIMESERIES ] [, ...] ) [ constraint_option [...] ] |
{ FOREIGN KEY (foreign_key_column [, ...] )
REFERENCES parent_table [ ( 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 Delta Lake table.
CONSTRAINT
nameSpecifies a name for the constraint. The name must be unique within the table. If no name is provided Azure Databricks will generate one.
CHECK (
condition)
condition
must be a deterministic expression returning aBOOLEAN
.condition
may be composed of literals, column identifiers within the table, and deterministic, built-in SQL functions or operators except:- Aggregate functions
- Analytic window functions
- Ranking window functions
- Table valued generator functions
Also
condition
must not contain any subquery.For a
CHECK
constraint to be satisfied in Azure Databricks it must evaluate totrue
.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
Important
This feature is in Public Preview.
Applies to:
Databricks SQL
Databricks Runtime 11.3 LTS and above
Defines an informational primary key or informational foreign key constraint for a Delta Lake table.
CONSTRAINT
nameOptionally specifies a name for the constraint. The name must be unique within the schema. If no name is provided Azure Databricks will generate one.
PRIMARY KEY ( key_column [ TIMESERIES ] [, ...] ) [ constraint_option [...] ]
Applies to:
Unity Catalog only
Adds a primary key constraint to the Delta Lake table. A table can have at most one primary key.
Primary key constraints are not supported for tables in the
hive_metastore
catalog.-
A column of the subject table defined as
NOT NULL
. Column names must not be repeated. TIMESERIES
Applies to:
Databricks SQL
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_table [ ( parent_column [, ...] ) ] foreign_key_option
Applies to:
Unity Catalog only
Adds a foreign key (referential integrity) constraint to the Delta Lake table.
Foreign key constraints are not supported for tables in the
hive_metastore
catalog.-
A column of the subject table. 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 ofparent_columns
. Two foreign keys cannot share an identical set of foreign key columns. -
Specifies the table the foreign key refers to. The table must have a defined
PRIMARY KEY
constraint, and you must have theSELECT
privilege on the table. -
A column in the parent table which is part of its primary key. All primary key columns of the parent table 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
Azure 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
orRELY
Applies to:
Databricks SQL
Databricks Runtime 14.2 and above for
PRIMARY KEY
constraintsApplies to:
Databricks Runtime 15.4 and above for
FOREIGN KEY
constraintsIf
RELY
, Azure 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 Azure Databricks takes no action to restrict the update or update the foreign key.ON DELETE NO ACTION
If the parent row is deleted Azure Databricks takes no action to restrict the action, update the foreign key, or delete the dependent row.
Important
Azure 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
-- 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);