mysql引用表无效列,mysql innodb：describe表不显示列引用，什么显示它们？

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)

);

if i execute 'describe bugs_products' i get:

Field | Type | Null | Key | Default | Extra |

+------------+---------+------+-----+---------+-------+

| bug_id | int(11) | NO | PRI | NULL | |

| product_id | int(11) | NO | PRI | NULL | |

+------------+---------+------+-----+---------+-------+

how can i also get references information?

解决方案

On testing, the foreign keys are not created on my machine using this syntax:

CREATE TABLE bugs (

...

reported_by VARCHAR(100) REFERENCES accounts(account_name),

...

) ENGINE = INNODB;

But they are when I use this create statement:

CREATE TABLE bugs (

...

reported_by VARCHAR(100),

...

FOREIGN KEY (reported_by) REFERENCES accounts(account_name)

) ENGINE = INNODB;

An easy way to see if foreign keys exist on a table is:

show create table bugs_products

Or you can query the information schema:

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'

Also check you're using the InnoDB storage engine. The MyISAM engine does not support foreign keys. You can find the engine like:

select table_schema, table_name, engine

from information_schema.TABLES

where table_name = 'bugs'

If you try to create a foreign key on a MyISAM table, it will silently discard the references and pretend to succeed.