TABLE_CONSTRAINTS
Important
This feature is in Public Preview.
Applies to: Databricks SQL Databricks Runtime 11.3 LTS and above Unity Catalog only
INFORMATION_SCHEMA.TABLE_CONSTRAINTS contains metadata for all primary and foreign key constraints within the catalog.
The rows returned are limited to the tables the user has permission to interact with.
Definition
The TABLE_CONSTRAINTS
relation contains the following columns:
Name | Data type | Nullable | Standard | Description |
---|---|---|---|---|
CONSTRAINT_CATALOG |
STRING |
No | Yes | Catalog containing the constraint. |
CONSTRAINT_SCHEMA |
STRING |
No | Yes | Schema containing the constraint. |
CONSTRAINT_NAME |
STRING |
No | Yes | Name of the constraint. |
TABLE_CATALOG |
STRING |
No | Yes | Catalog containing the table. |
TABLE_SCHEMA |
STRING |
No | Yes | Schema containing the table. |
TABLE_NAME |
STRING |
No | Yes | Name of the table in which the constraint is defined. |
CONSTRAINT_TYPE |
STRING |
No | Yes | One of 'CHECK' , 'PRIMARY KEY' , 'FOREIGN KEY' |
IS_DEFERRABLE |
STRING |
No | Yes | Always'YES' . Reserved for future use. |
INITIALLY_DEFERRED |
STRING |
No | Yes | Always 'YES' . Reserved for future use. |
ENFORCED |
STRING |
No | Yes | 'YES' if constraint is enforced, 'NO' otherwise. |
COMMENT |
STRING |
Yes | No | Optional description of the constraint. |
Constraints
The following constraints apply to the TABLE_CONSTRAINTS
relation:
Class | Name | Column List | Description |
---|---|---|---|
Primary key | TAB_CONS_PK |
CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME |
Uniquely identifies the relation. |
Foreign key | TAB_CONS_TABLES_FK |
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME |
References TABLES_PK |
Examples
> SELECT constraint_name
FROM information_schema.constraint_table_usage
WHERE table_schema = 'information_schema'
AND table_name = 'tables'
AND constraint_type = 'CHECK'