The view information_schema.referential_constraints contains all referential constraints (aka foreign keys) in the database.
SELECT * FROM information_schema.referential_constraints;
SELECT constraint_schema, constraint_name, fk_table_name, uc_table_name
FROM information_schema.referential_constraints;
information_schema.referential_constraints
| column name | type | remarks |
|---|---|---|
| constraint_catalog | VARCHAR | Always NULL |
| constraint_schema | VARCHAR | |
| constraint_name | VARCHAR | |
| unique_constraint_catalog | VARCHAR | Always NULL |
| unique_constraint_schema | VARCHAR | |
| unique_constraint_name | VARCHAR | |
| match_option | VARCHAR | FULL or PARTIAL or NONE, currently always FULL |
| update_rule | VARCHAR | CASCADE or RESTRICT or SET DEFAULT or SET NULL or NO ACTION |
| delete_rule | VARCHAR | CASCADE or RESTRICT or SET DEFAULT or SET NULL or NO ACTION |
| fk_schema_id | INTEGER | reference to sys.schemas.id |
| fk_table_id | INTEGER | reference to sys.tables.id |
| fk_table_name | VARCHAR | name of table which contains the foreign key |
| fk_key_id | INTEGER | reference to sys.fkeys.id |
| uc_schema_id | INTEGER | reference to sys.schemas.id |
| uc_table_id | INTEGER | reference to sys.tables.id |
| uc_table_name | VARCHAR | name of table referenced by the foreign key |
| uc_key_id | INTEGER | reference to sys.keys.id |
Note: The last 8 columns (fk_schema_id, fk_table_id, fk_table_name, fk_key_id, uc_schema_id, uc_table_id, uc_table_name and uc_key_id) 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.