查询以查找外键 [英] Query to find foreign keys

问题描述

我有一个数据库，我需要删除一些外键，但我事先不知道外键是否仍然存在.

I have a database where I need to drop some foreign keys, but I don't know beforehand whether the foreign keys still exist.

我找到了一些存储过程 (http://forums.mysql.com/read.php?97,218825,247526) 可以解决问题，但我不想为此创建存储过程.

I've found some stored procedures (http://forums.mysql.com/read.php?97,218825,247526) that does the trick, but I don't want to create a stored procedure for this.

我尝试在存储过程中使用查询，但使用IF EXISTS (SELECT NULL FROM etc.. etc...

I've tried to use the query inside the stored procedure, but I get an error using "IF EXISTS (SELECT NULL FROM etc.. etc...

我只能在存储过程中使用 IF EXISTS 吗?

Can I only use IF EXISTS in stored procedures?

现在，我唯一能跑的就是

right now, the only thing I can run is

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'myschema'
AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'mytable';

我也试过了

IF EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = parm_key_name) THEN
(...) do something (...)
END IF; 

但我得到一个 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 1 行的IF"附近使用正确的语法

我在论坛中查找了带有简单查询的示例，但我无法理解为什么这不起作用.

I've looked for examples in forums with simple queries and I can't make sense of why this isn't working.

注意:编辑以更正断开的链接

NOTE: Edit to correct broken link

推荐答案

需要连接Information scheme，可以在这个表中找到所有主键和外键的信息

You need to connect to the Information scheme and you can find all the information about the primary key and foreign keys in this table

SELECT * FROM information_schema.TABLE_CONSTRAINTS T;

您需要成为 ROOT 用户才能访问 information_schema.

you need to be a ROOT user to access the information_schema.

使用这个表可以查到表、db以及是否有外键.

USING this table you can find the table, db and whether it has foreign key.

如果您不想使用 IF EXIST 和存储过程，希望这会有所帮助.但我确信你可以使用 IF EXIST 可以用于非存储过程查询....

Hope this helps if you dont wanna use IF EXIST and Stored Procedure. But I am Sure you can use IF EXIST can be used for non stored procedure queries....

这篇关于查询以查找外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！