The view information_schema.table_constraints contains all table constraints defined on tables in the database.
It includes CHECK
, PRIMARY KEY
, FOREIGN KEY
and UNIQUE
constraints.
SELECT * FROM information_schema.table_constraints;
SELECT constraint_schema, constraint_name, table_name, constraint_type
FROM information_schema.table_constraints WHERE NOT is_system;
information_schema.table_constraints
column name | type | remarks |
---|---|---|
constraint_catalog | VARCHAR | Always NULL |
constraint_schema | VARCHAR | |
constraint_name | VARCHAR | |
table_catalog | VARCHAR | Always NULL |
table_schema | VARCHAR | |
table_name | VARCHAR | |
constraint_type | VARCHAR | CHECK or PRIMARY KEY or FOREIGN KEY or UNIQUE or UNIQUE NULLS NOT DISTINCT |
is_deferrable | VARCHAR | NO or YES |
initially_deferred | VARCHAR | NO or YES |
enforced | VARCHAR | NO or YES |
schema_id | INTEGER | reference to sys.schemas.id |
table_id | INTEGER | reference to sys.tables.id |
key_id | INTEGER | reference to sys.keys.id |
key_type | INTEGER | reference to sys.key_types.key_type_id |
is_system | BOOLEAN | true when it is a constraint for a system table, else false |
Note: The last 5 columns (schema_id, table_id, key_id, key_type and is_system) are extensions to the view as defined by ISO standard. They provide useful information and simplify filtering and joins with system tables/views in sys schema when needed.