mysql删除外键时查询_外键查询及删除

```

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;

```