SELECT Concat(TABLE_NAME, '.', COLUMN_NAME) AS 'foreign key',
Concat(referenced_table_name, '.', referenced_column_name) AS 'references'
FROM information_schema.key_column_usage
WHERE table_schema = '$databasename'
AND referenced_table_name IS NOT NULL;

其中, ‘$databasename’ 要替换成您要查询的那个数据库的名称。

 


mysql> SHOW CREATE TABLE mk_big_sent_visitor\G
*************************** 1. row ***************************
Table: mk_big_sent_visitor
Create Table: CREATE TABLE
mk_big_sent_visitor (
id int(11) NOT NULL AUTO_INCREMENT,
mk_big_sent_id int(11) NOT NULL,
customer_id int(11) NOT NULL,
PRIMARY KEY (
id),
KEY
fk_mk_big_sent_visitor_mk_big_sent1_idx (mk_big_sent_id),
KEY
fk_mk_big_sent_visitor_customer1_idx (customer_id),
CONSTRAINT
fk_mk_big_sent_visitor_customer1 FOREIGN KEY (customer_id) REFERENCES customer (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT
fk_mk_big_sent_visitor_mk_big_sent1 FOREIGN KEY (mk_big_sent_id) REFERENCES mk_big_sent (id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''
1 row in set (0.00 sec)

删除外键:


ALTER TABLE mk_prize DROP FOREIGN KEY fk_think_mk_prize_shopweb1,

DROP FOREIGN KEY fk_mk_big_sent_visitor_mk_big_sent1;