RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-create-table-constraint below:

CONSTRAINT clause | Databricks Documentation

CONSTRAINT clause

Applies to: Databricks SQL Databricks Runtime

Adds an informational primary key or an informational foreign key as part of a CREATE TABLE or CREATE MATERIALIZED VIEW statement.

To add a check constraint to a Delta Lake table use ALTER TABLE after the table has been created.

Syntaxâ

Use the table_constraint clause to define constraints which span multiple columns or to separate the syntax away from the column definition.

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

Use the column_constraintclause to define constraints specific to a single column definition.

column_constraint
  { [ CONSTRAINT name ]
    { PRIMARY KEY [ constraint_option ] [...] |
      { [ FOREIGN KEY ]
        REFERENCES parent_table [ ( parent_column [, ...] ) ]
        [ foreign_key_option | constraint_option ] [...]
      }
    }
  }

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

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â

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: Databricks SQL Databricks Runtime 11.3 LTS and above Unity Catalog only

Adds an informational primary key constraint to the table or materialized view. A table or materialized view can have at most one primary key.

Primary key columns are implicitly defined as NOT NULL.

Primary key constraints are not supported for tables in the hive_metastore catalog.
- key_column
  
  A column of the subject table or materialized view. 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.
PRIMARY KEY [ constraint_option ] [...]

Adds a single column primary key constraint to the table or materialized view, using the preceding key column definition.

This column_constraint is equivalent to the table_constraint

PRIMARY KEY (key_column) [ constraint_option ] [...]
FOREIGN KEY (foreign_key_column [, ...] ) REFERENCES parent_table [ ( parent_column [, ...] ) ] foreign_key_option

Applies to: Databricks SQL Databricks Runtime 11.3 LTS and above Unity Catalog only

Adds an informational foreign key (referential integrity) constraint to the table or materialized view.

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

Foreign key constraints which only differ in the permutation of the foreign key columns are not allowed.
- foreign_key_column
  
  A column of the subject table or materialized view. 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_table
  
  Specifies the table or materialized view the foreign key refers to. The table must have a defined PRIMARY KEY constraint, and you must have the SELECT privilege on the table.
- parent_column
  
  A column in the parent table or materialized view which is part of its primary key. All primary key columns of the parent table or materialized view must be listed.
  
  If parent columns are not listed, they are specified by the order given in the PRIMARY KEY definition.
- FOREIGN KEY REFERENCES parent_table [ ( parent_column ) ] foreign_key_option
  
  Adds a single column foreign key constraint to the table or materialized view, using the preceding foreign key column definition.
  
  This column_constraint is equivalent to the table_constraint
  
  FOREIGN KEY ( foreign_key_column ) REFERENCES parent_table [ ( parent_column ) ] foreign_key_option
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: Databricks SQL Databricks Runtime 14.2 and above for PRIMARY KEY constraints
  
  Applies to: Databricks SQL Databricks Runtime 15.4 and above for FOREIGN KEY constraints
  
  If RELY, Databricks may exploit the constraint to rewrite and optimize 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. Query optimizations associated with the RELY command require queries to run on Photon-enabled compute. See What is Photon?.
  
  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


> CREATE TABLE persons(first_name STRING NOT NULL, last_name STRING NOT NULL, nickname STRING,
                       CONSTRAINT persons_pk PRIMARY KEY(first_name, last_name));


> CREATE TABLE pets(name STRING, owner_first_name STRING, owner_last_name STRING,
                    CONSTRAINT pets_persons_fk FOREIGN KEY (owner_first_name, owner_last_name) REFERENCES persons);


> CREATE TABLE customers(customerid STRING NOT NULL PRIMARY KEY, name STRING);


> CREATE TABLE orders(orderid BIGINT NOT NULL CONSTRAINT orders_pk PRIMARY KEY,
                      customerid STRING CONSTRAINT orders_customers_fk REFERENCES customers);