For a Table:
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = '<database>' AND
REFERENCED_TABLE_NAME = '<table>';
For a Column:
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = '<database>' AND
REFERENCED_TABLE_NAME = '<table>' AND
REFERENCED_COLUMN_NAME = '<column>';
Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.
I found the generated set of drop statements useful, and recommend these tweaks:
- Limit the generated drops to your database like this:
SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';
Note 1: This does not execute the DROP statements, it just gives you a list of them. You will need to cut and paste the output into your SQL engine to execute them.
Note 2: If you have VIEWs, you'll have to correct each DROP TABLE `VIEW_NAME`
statement to DROP VIEW `VIEW_NAME`
manually.
- Note, per http://dev.mysql.com/doc/refman/5.5/en/drop-table.html, dropping with cascade is pointless / misleading:
"RESTRICT and CASCADE are permitted to make porting easier. In MySQL 5.5, they do nothing."
Therefore, in order for the drop statements to work if you need:
SET FOREIGN_KEY_CHECKS = 0
This will disable referential integrity checks - so when you are done performing the drops you need, you will want to reset key checking with
SET FOREIGN_KEY_CHECKS = 1
- The final execution should look like:
SET FOREIGN_KEY_CHECKS = 0;
-- Your semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;
NB: to use output of SELECT easier, mysql -B option can help.
Best Answer
The performance of each storage engine will depend on the queries you perform. However, be aware that different tables within the same database can use different storage engines.
As documented under Foreign Key Differences:
Therefore, foreign key constraints have simply not yet been implemented in
MyISAM
.EDIT: As that comment is removed from docs, it appears that it is no longer planned to implement foreign key constraints in
MyISAM
engine.As documented under What Is New in MySQL 5.6:
Therefore, full text search has been implemented in
InnoDB
as of MySQL 5.6.