The INNODB_SYS_FOREIGN table provides metadata about foreign key constraints defined on InnoDB tables, including reference details.
The Information Schema INNODB_SYS_FOREIGN table contains information about InnoDB foreign keys.
The PROCESS privilege is required to view the table.
It has the following columns:
ID
Foreign key name. Prior to , this is preceded by the database name. From MariaDB 12.1, foreign key names are only required to be unique per table, not per database, so the redundant database name is not shown.
The TYPE column provides a bit flag with information about the foreign key. This information is OR'ed together to read:
Prior to :
From MariaDB 12.1:
This page is licensed: CC BY-SA / Gnu FDL
FOR_NAME
Database and table name of the foreign key child.
REF_NAME
Database and table name of the foreign key parent.
N_COLS
Number of foreign key index columns.
TYPE
Bit flag providing information about the foreign key.
1
ON DELETE CASCADE
2
ON UPDATE SET NULL
4
ON UPDATE CASCADE
8
ON UPDATE SET NULL
16
ON DELETE NO ACTION
32
ON UPDATE NO ACTION
ELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN\G
*************************** 1. row ***************************
ID: test/fk_book_author
FOR_NAME: test/book
REF_NAME: test/author
N_COLS: 1
TYPE: 1
...SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN\G
*************************** 1. row ***************************
ID: fk_book_author
FOR_NAME: test/book
REF_NAME: test/author
N_COLS: 1
TYPE: 1