Foreign Key Search Request

Question

I have a database where I need to delete some foreign keys, but I do not know in advance whether foreign keys exist.

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

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

Can I 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'; 

and I tried it too

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

but I get You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF' at line 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF' at line 1

I searched for examples on forums with simple queries, and I can't figure out why this is not working.

NOTE. Edit to fix broken link

Answer 1

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;

you need to be ROOT to access information_schema .

The USE of this table can be found in the table, db and whether it has a foreign key.

Hope this helps if you don't want to use IF EXIST and the stored procedure. But I'm sure you can use IF EXIST , which can be used for queries without stored procedures ....

Answer 2

Why not use the INFORMATION_SCHEMA table?

 SELECT * FROM `TABLE_CONSTRAINTS` WHERE `CONSTRAINT_TYPE` = 'FOREIGN KEY' 

Answer 3

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 concat(table_name, '.', column_name) as 'foreign key', concat(referenced_table_name, '.', referenced_column_name) as 'references' from information_schema.key_column_usage where referenced_table_name is not null; 

HELP: see list-foreign-keys-in-mysql link