REFERENTIAL_CONSTRAINTS

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

Preview

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';