mysql show create table_mysql – SHOW CREATE TABLE输出中缺少外键约束

我发现SHOW CREATE TABLE没有像我期望的那样显示外键约束.

为了演示,这是MySQL manual的一个例子：

CREATE TABLE parent (

id INT NOT NULL,

PRIMARY KEY (id)

) ENGINE=INNODB;

CREATE TABLE child (

id INT, parent_id INT,

INDEX par_ind (parent_id),

FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE

) ENGINE=INNODB;

mysql> SHOW CREATE TABLE child\G

*************************** 1. row ***************************

Table: child

Create Table: CREATE TABLE "child" (

"id" int(11) default NULL,

"parent_id" int(11) default NULL,

KEY "par_ind" ("parent_id")

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

在那个输出中,我希望看到类似的东西：

CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)

REFERENCES `parent` (`id`) ON DELETE CASCADE

在那创建表输出,但显然它不存在.

然而,约束确实存在：

mysql> SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE table_schema=database()\G

*************************** 1. row ***************************

CONSTRAINT_CATALOG: NULL

CONSTRAINT_SCHEMA: test_fk

CONSTRAINT_NAME: child_ibfk_1

TABLE_CATALOG: NULL

TABLE_SCHEMA: test_fk

TABLE_NAME: child

COLUMN_NAME: parent_id

ORDINAL_POSITION: 1

POSITION_IN_UNIQUE_CONSTRAINT: 1

REFERENCED_TABLE_SCHEMA: test_fk

REFERENCED_TABLE_NAME: parent

REFERENCED_COLUMN_NAME: id

*************************** 2. row ***************************

CONSTRAINT_CATALOG: NULL

CONSTRAINT_SCHEMA: test_fk

CONSTRAINT_NAME: PRIMARY

TABLE_CATALOG: NULL

TABLE_SCHEMA: test_fk

TABLE_NAME: parent

COLUMN_NAME: id

ORDINAL_POSITION: 1

POSITION_IN_UNIQUE_CONSTRAINT: NULL

REFERENCED_TABLE_SCHEMA: NULL

REFERENCED_TABLE_NAME: NULL

REFERENCED_COLUMN_NAME: NULL

2 rows in set (0.01 sec)

mysql> SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE table_schema=database()\G

*************************** 1. row ***************************

CONSTRAINT_CATALOG: NULL

CONSTRAINT_SCHEMA: test_fk

CONSTRAINT_NAME: child_ibfk_1

TABLE_SCHEMA: test_fk

TABLE_NAME: child

CONSTRAINT_TYPE: FOREIGN KEY

*************************** 2. row ***************************

CONSTRAINT_CATALOG: NULL

CONSTRAINT_SCHEMA: test_fk

CONSTRAINT_NAME: PRIMARY

TABLE_SCHEMA: test_fk

TABLE_NAME: parent

CONSTRAINT_TYPE: PRIMARY KEY

2 rows in set (0.01 sec)

我用自己设计的例子得到了相同的结果.

知道发生了什么事吗？

更新2011-01-08：我认为这与sql_mode变量有关.但目前我不知道哪种模式设置排除了SHOW CREATE TABLE输出的约束.