Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 36.5Mb
HTML Download (TGZ) - 9.9Mb
HTML Download (Zip) - 9.9Mb
HTML Download (RPM) - 8.7Mb
Man Pages (TGZ) - 209.5Kb
Man Pages (Zip) - 318.7Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table

24.32.19 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table

The INNODB_SYS_FOREIGN table provides metadata about InnoDB foreign keys, equivalent to the information from the SYS_FOREIGN table in the InnoDB data dictionary.

For related usage information and examples, see Section 14.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.

The INNODB_SYS_FOREIGN table has these columns:

  • ID

    The name (not a numeric value) of the foreign key index. Preceded by the database name, for example, test/products_fk.

  • FOR_NAME

    The name of the child table in this foreign key relationship.

  • REF_NAME

    The name of the parent table in this foreign key relationship.

  • N_COLS

    The number of columns in the foreign key index.

  • TYPE

    A collection of bit flags with information about the foreign key column, ORed together. 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

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN\G
*************************** 1. row ***************************
      ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1

Notes

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.