The view information_schema.table_constraints contains all table constraints defined on tables in the database.
It includes CHECK, PRIMARY KEY, FOREIGN KEY, UNIQUE and UNIQUE NULLS NOT DISTINCT constraints.
It excludes NOT NULL column constraints. Those can be queried from view information_schema.columns column is_nullable.
SELECT * FROM information_schema.table_constraints;
SELECT constraint_schema, table_name, constraint_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.