CODEWITHSUNDEEP
mysqlforeign-keysinnodbinformation-schema
DaedalusFall
DaedalusFall

Reputation: 8575

Determine InnoDB FK Constraints without information_schema

I'm writing some code to inspect a MySQL database structure, and need information about Foreign Key constraints (on InnoDB tables).

There are two ways I know of to do this:

  1. Parse the results of SHOW CREATE TABLE X
  2. Use INFORMATION_SCEMA.REFERENTIAL_CONSTRAINTS

Unfortunately option two requires MySQL 5.1.16 or later, so I can't use it unless/until I can convince our server guy to update, And while I can probably get away with option 1, it feels messy and without writing a full SQL parser I wouldn't feel sure my code would always work with any table.

Is there another way of getting at this information?

Thanks

Upvotes: 0

Views: 298

Answers (1)

Conspicuous Compiler
Conspicuous Compiler

Reputation: 6469

From the MySQL 5.0 manual online:

You can also display the foreign key constraints for a table like this:

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

The foreign key constraints are listed in the Comment column of the output.

Poster indicates that this doesn't provide ON UPDATE and ON DELETE information which is an important part of foreign key behavior.

Another option:

Since you control the code involved, is it possible to set up another MySQL instance in the same environment which is version 5.1+? If so, let's call that instance dummy. Run the SHOW CREATE TABLE on the live database. Then, on dummy run a DROP TABLE IF EXIST followed by the output from the SHOW CREATE TABLE query.

Now you can use INFORMATION_SCHEMA on the dummy database to get the information.

Upvotes: 1

Related Questions