Information Schema INNODB_SYS_FOREIGN Table

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:

Column	Description
ID	Foreign key name. Prior to MariaDB 12.1, 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.
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.

The TYPE column provides a bit flag with information about the foreign key. This information is OR'ed together to read:

Bit Flag	Description
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

Example

Prior to MariaDB 12.1:

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
...

From MariaDB 12.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

_{This page is licensed: CC BY-SA / Gnu FDL}