外键查询及删除

SQL查询数据库中表的外键关系并删除特定外键

最新推荐文章于 2025-09-11 20:53:34 发布

转载最新推荐文章于 2025-09-11 20:53:34 发布 · 69 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/martinjinyu/p/4711756.html

文章标签：

#数据库

本文介绍了如何使用SQL查询语言从数据库中获取表的外键信息，并通过示例展示了如何删除指定的外键。重点讨论了在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;
```

转载于:https://www.cnblogs.com/martinjinyu/p/4711756.html