1

How do I get a list of all foreign key constraints pointing TO (and FROM) a particular table and/or a particular column from a MySQL database built with MyISAM? This is the same as this InnoDB question, but MyISAM specific for MySQL.

Consider the canonical airlines dataset. When I run the following two queries based on the linked question's accepted answer (TO) and Node's answer (FROM), my results are empty set.

Show all the foreign keys pointing TO 'mytable':

SELECT 
  TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = 'airlines' AND  -- <database>
  REFERENCED_TABLE_NAME = 'airports';  -- <table> flights, carriers, planes

Show all the foreign keys pointing FROM 'mytable':

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

Am I misidentifying the REFERENCED_TABLE_SCHEMA? Does this query only work in InnoDB?

Karl Baker
  • 903
  • 12
  • 27
  • 4
    MyISAM does not support foreign keys; MySql allows them in CREATE TABLE but ignores them if the engine is MyISAM. – Uueerdo Apr 08 '19 at 18:40

1 Answers1

3

https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html says:

Foreign key support: No

It's not recommended to use MyISAM at all these days. MySQL is clearly moving to phase it out.

See also my answer to MyISAM versus InnoDB for a good demonstration why we should all avoid using MyISAM.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • It's also super easy to switch with `ALTER TABLE x ENGINE=InnoDB`. – tadman Apr 08 '19 at 19:42
  • @tadman I'm new to SQL and MySQL. Can I ALTER all tables at once? – Karl Baker Apr 08 '19 at 23:45
  • If you back up everything first, yes, it's usually a straight-forward process to alter them all. The easy way is with [MySQL Workbench](https://dev.mysql.com/downloads/workbench/) where you can flip a drop-down and have them converted automatically. You can also script this pretty easily if there's a lot of tables to do, you just need to generate the right number of `ALTER TABLE` statements. There's many ways of generating this, from a scripting language to Notepad/some editor to Excel/Google Docs, so long as you get the commands in the right format. – tadman Apr 08 '19 at 23:57
  • Do this on a test copy of your database first to shake out any potential problems. Most of the time it's fine, but it's worth knowing if there's hitches. Each conversion can take time and will require locking the table until the conversion is complete so plan for some down-time here. – tadman Apr 08 '19 at 23:57
  • In other words, no, you can't alter all tables at once. You can alter them one at a time, but it is not so hard to generate the series of commands to do that. – Bill Karwin Apr 08 '19 at 23:58
  • Also in some cases, InnoDB tables take more space than equivalent MyISAM tables, so be aware of that and make sure you have plenty of storage space. Count on 2x storage space probably being the upper bound, but it depends on your tables and indexes. – Bill Karwin Apr 08 '19 at 23:59