一個DD CONSTRAINT clause

Preview

This feature is inPublic Preview.

一個pplies to:check marked yesDatabricks SQLcheck marked yesDatabricks Runtime

一個dds aninformationalprimary key,informationalforeign key, or an enforced check constraint to an existing Delta Lake table.

Syntax

一個DD[check_constraint|key_constraint]check_constraintCONSTRAINTnameCHECK(condition)[ENFORCED]key_constraint{[CONSTRAINTname]{PRIMARYKEY(key_column[,...])[constraint_option][...]|{FOREIGNKEY(foreign_key_column[,...])REFERENCESparent_table[(parent_column[,...]])[constraint_option|foreign_key_option][...]}}constraint_option{NOTENFORCED|DEFERRABLE|INITIALLYDEFERRED|NORELY}foreign_key_option{MATCHFULL|ONUPDATENO一個CTION|ONDELETENO一個CTION}

For compatibility with non-standard SQL dialects you can specifyENABLENOVALIDATEinstead ofNOTENFORCEDDEFERRABLEINITIALLYDEFERRED.

Parameters

  • check_constraint

    Defines a check constraint for a Delta Lake table.

    • CONSTRAINTname

      Specifies a name for the constraint. The name must be unique within the table. If no name is provided Databricks will generate one.

    • CHECK(condition)

      conditionmust be a deterministic expression returning a BOOLEAN.

      condition可能是由文字組成的,列標識符within the table, and deterministic, built-in SQL functions or operators except:

      一個lsoconditionmust not contain anysubquery.

      For a CHECK constraint to be satisfied in 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

    Preview

    This feature is inPublic Preview.

    一個pplies to:check marked yesDatabricks SQLcheck marked yesDatabricks Runtime 11.1 and above

    Defines aninformationalprimary key orinformationalforeign key constraint for a Delta Lake table.

    • CONSTRAINTname

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

    • PRIMARYKEY(key_column[,...])constraint_option

      一個pplies to:check marked yesUnity Catalog only

      一個dds 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 thehive_metastorecatalog.

    • key_column

      一個column of the subject table defined asNOTNULL. Column names must not be repeated.

    • FOREIGNKEY(foreign_key_column[,...])REFERENCESparent_table[(parent_column[,...])]foreign_key_option

      一個pplies to:check marked yesUnity Catalog only

      一個dds a foreign key (referential integrity) constraint to the Delta Lake table.

      Foreign key constraints are not supported for tables in thehive_metastorecatalog.

      • foreign_key_column

        一個column of the subject table. Column names must not be repeated. The data type of each column must match the type of the matchingparent_column. The number of columns must match the number ofparent_columns. Two foreign keys cannot share an identical set of foreign key columns.

      • parent_table

        Specifies the table the foreign key refers to. The table must have a definedPRIMARYKEYconstraint, and you must own that table.

      • parent_column

        一個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 thePRIMARYKEYdefinition.

      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.

      • NOTENFORCED

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

      • DEFERRABLE

        The constraint enforcement can be deferred.

      • INITIALLYDEFERRED

        Constraint enforcement is deferred.

      • NORELY

        Databricks does not exploit the constraint to rewrite a query.

    • 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.

      • MATCHFULL

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

      • ONUPDATENO一個CTION

        If the parentPRIMARYKEYis updated Databricks takes no action to restrict the update or update the foreign key.

      • ONDELETENO一個CTION

        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

-- Add a primary key>CREATETABLEpersons(first_nameSTRINGNOTNULL,last_nameSTRINGNOTNULL,nicknameSTRING);>一個LTERTABLEpersons一個DDCONSTRAINTpersons_pkPRIMARYKEY(first_name,last_name);-- Add a foreign key>CREATETABLEpets(nameSTRING,owner_first_nameSTRING,owner_last_nameSTRING);>一個LTERTABLEpets一個DDCONSTRAINTpets_persons_fkFOREIGNKEY(owner_first_name,owner_last_name)REFERENCESpersons;-- Add a check contraint>一個LTERTABLEpets一個DDCONSTRAINTpets_name_not_cute_chkCHECK(length(name)<20);