SHOW CREATE TABLE输出中缺少外键约束 [英] Foreign key constraints missing from SHOW CREATE TABLE output

问题描述

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

I'm finding that SHOW CREATE TABLE is not showing foreign key constraints as I would expect.

为了演示，这是来自的示例MySQL手册:

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)

在该输出中，我希望看到类似的东西:

In that output I would have expected to see something like:

CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
REFERENCES `parent` (`id`) ON DELETE CASCADE

在创建表的输出中，但显然它不存在.

in that create table output but clearly it's not there.

但是该约束确实存在:

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)

通过我自己的设计示例，我得到了相同的结果.

I get the same results with examples of my own design.

知道发生了什么事吗?

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

UPDATE 2011-01-08: I think this has something to do with the sql_mode variable. But at the moment I don't know which mode setting excludes constraints from SHOW CREATE TABLE output.

推荐答案

mysql sql_mode变量的设置绝对是我报告的行为的原因.我从my.cnf中删除了设置它的行，并且mysql执行了预期的行为.但是我忘了那个设置是什么，从那以后一直没有弄清楚.如果任何人都可以识别出哪个，请继续.

The setting of the mysql sql_mode variable was definitely the cause of the behavior I reported. I deleted the line that set it from my.cnf and mysql performed the expected behavior. But I forget what that setting was and haven't figured it out since. If anyone can identify which, go right ahead.

这篇关于SHOW CREATE TABLE输出中缺少外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！