CODEWITHSUNDEEP
mysqlforeign-keysmyisam
Karl Baker
Karl Baker

Reputation: 913

MySQL MyISAM How do I see all foreign keys TO and FROM a table or column?

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?

Upvotes: 1

Views: 341

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

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.

Upvotes: 3

Related Questions