mysql innodb : describe table does not show columns references, 显示了什么？

Question

CREATE TABLE accounts (
 account_name      VARCHAR(100) NOT NULL PRIMARY KEY
);

CREATE TABLE products (
 product_id        INTEGER NOT NULL PRIMARY KEY,
 product_name      VARCHAR(100)
);

CREATE TABLE bugs (
  bug_id            INTEGER NOT NULL PRIMARY KEY,
  bug_description   VARCHAR(100),
  bug_status        VARCHAR(20),
  reported_by       VARCHAR(100) REFERENCES accounts(account_name),
  assigned_to       VARCHAR(100) REFERENCES accounts(account_name),
  verified_by       VARCHAR(100) REFERENCES accounts(account_name)
 );

CREATE TABLE bugs_products (
  bug_id            INTEGER NOT NULL REFERENCES bugs,
  product_id        INTEGER NOT NULL REFERENCES products,
  PRIMARY KEY       (bug_id, product_id)
);

如果我执行“描述 bugs_products”我得到:

 Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| bug_id     | int(11) | NO   | PRI | NULL    |       | 
| product_id | int(11) | NO   | PRI | NULL    |       | 
+------------+---------+------+-----+---------+-------+

我怎样才能获得引用信息？

Answer 1

在测试中，外键不是使用以下语法在我的计算机上创建的:

CREATE TABLE bugs (
  ...
  reported_by       VARCHAR(100) REFERENCES accounts(account_name),
  ...
 ) ENGINE = INNODB;

但是当我使用这个 create 语句时它们是:

CREATE TABLE bugs (
  ...
  reported_by       VARCHAR(100),
  ...
  FOREIGN KEY (reported_by) REFERENCES accounts(account_name)
 ) ENGINE = INNODB;

查看表中是否存在外键的简单方法是:

show create table bugs_products

或者您可以查询信息架构:

select
  table_schema
, table_name
, column_name
, referenced_table_schema
, referenced_table_name
, referenced_column_name
from information_schema.KEY_COLUMN_USAGE
where table_name = 'bugs'

还要检查您正在使用 InnoDB 存储引擎。 MyISAM 引擎不支持外键。您可以像这样找到引擎:

select table_schema, table_name, engine
from information_schema.TABLES
where table_name = 'bugs'

如果您尝试在 MyISAM 表上创建外键，它会默默地丢弃引用并假装成功。