REFERENTIAL_CONSTRAINTS

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above check marked yes Unity Catalog only

Important

This feature is in Public Preview.

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS describes the referential integrity (RI) relationships between foreign keys and primary keys.

The rows returned are limited to constraints on the tables the user is privileged to interact with.

Definition

The REFERENTIAL_CONSTRAINTS relation contains the following columns:

Name Data type Nullable Standard Description
CONSTRAINT_CATALOG STRING No Yes Catalog containing the foreign key constraint.
CONSTRAINT_SCHEMA STRING No Yes Schema containing the foreign key constraints.
CONSTRAINT_NAME STRING No Yes Name of the check constraint.
UNIQUE_CONSTRAINT_CATALOG STRING No Yes Catalog containing the referenced constraint.
UNIQUE_CONSTARINT_SCHEMA STRING No Yes Schema containing the referenced constraint.
UNIQUE_CONSTRAINT_NAME STRING No Yes Name of the referenced constraint.
MATCH_OPTION STRING No Yes Always FULL, reserved for future use..
UPDATE_RULE STRING No Yes Always NO ACTION, reserved for future use.
DELETE_RULE STRING No Yes Always NO ACTION, reserved for future use.

Constraints

The following constraints apply to the REFERENTIAL_CONSTRAINTS relation:

Class Name Column List Description
Primary key REF_CONS_PK CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME Unique identifier for the constraint.
Foreign key REF_CONS_TAB_CONS_FK_FK CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME References TABLE_CONSTRAINTS.
Foreign key REF_CONS_TAB_CONS_UC_FK UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME References TABLE_CONSTRAINTS.

Examples

> SELECT constraint_name, check_clause
    FROM information_schema.referential_constraints
    WHERE table_schema = 'information_schema';