The view information_schema.table_constraints contains all table constraints defined on tables in the database.
It includes PRIMARY KEY, UNIQUE and FOREIGN KEY 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 | PRIMARY KEY or UNIQUE or FOREIGN KEY |
| 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.