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.