INNODB_SYS_FOREIGN

• 该表提供查询有关InnoDB外键的元数据信息，等同于InnoDB数据字典中SYS_FOREIGN表的信息

• 该表为memory引擎临时表，查询该表的用户需要有process权限

• 表定义语句

CREATE TEMPORARY TABLE `INNODB_SYS_FOREIGN` (
  `ID` varchar(193) NOT NULL DEFAULT '',
  `FOR_NAME` varchar(193) NOT NULL DEFAULT '',
  `REF_NAME` varchar(193) NOT NULL DEFAULT '',
  `N_COLS` int(11) unsigned NOT NULL DEFAULT '0',
  `TYPE` int(11) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8

• 表字段含义

• ID：外键约束的名称（注意不是数值，该字符串是约束名称，不是索引名称），该ID值由外键名称字符串+数据库名称组成，例如："test/products_fk"
• FOR_NAME：外键相关的子表的名称(即外键索引列所在的表)
• REF_NAME：外键相关的父表的名称(即外键索引列引用列所在的表)
• N_COLS：外键索引中的列数量
• TYPE：带有关于外键列的信息的位标志集合，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

• 表记录内容示例

root@localhost : (none) 11:44:09> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
+-------------------------------+------------------------+-----------------------+--------+------+
| ID                            | FOR_NAME              | REF_NAME              | N_COLS | TYPE |
+-------------------------------+------------------------+-----------------------+--------+------+
| employees/dept_emp_ibfk_1    | employees/dept_emp    | employees/employees  |      1 |    1 |
| employees/dept_emp_ibfk_2    | employees/dept_emp    | employees/departments |      1 |    1 |
| employees/dept_manager_ibfk_1 | employees/dept_manager | employees/employees  |      1 |    1 |
| employees/dept_manager_ibfk_2 | employees/dept_manager | employees/departments |      1 |    1 |
| employees/salaries_ibfk_1    | employees/salaries    | employees/employees  |      1 |    1 |
| employees/titles_ibfk_1      | employees/titles      | employees/employees  |      1 |    1 |
+-------------------------------+------------------------+-----------------------+--------+------+
6 rows in set (0.00 sec)

---

上一篇：INNODB_SYS_COLUMNS表 |下一篇：INNODB_SYS_TABLESTATS表