0

I'm using MySQL 5.5.37. Given a group of tables, (table_a, table_b, ..., table_n), how do I write an SQL statement (or procedure) that will generate the SQL that produces all the foreign keys that these tables link to? Note that although I can run a mysqldump that will produce "CREATE TABLE" statements with "CONSTRAINT" clauses, I want ONLY the statmeents that will add the foreign keys, e.g.

ALTER TABLE table_a ADD FOREIGN KEY (P_Id) REFERENCES table_x(P_Id)

Thanks, - Dave

Dave
  • 15,639
  • 133
  • 442
  • 830
  • Are the foreign key constraints already in the schema? If so, you can get the information from the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table. – Barmar Apr 21 '14 at 20:52
  • Yes, the foreign key constraints are already in teh schema. I'm still a little hazy, though on how I'd use the INFORMATION_SCHEMA.KEY_COLUMN_USAGE to generate the correct "ALTER TABLE ... ADD CONSTRAINT" statements. Do you have any examples? – Dave Apr 21 '14 at 21:26
  • Does this help? http://stackoverflow.com/a/201678/1491895 – Barmar Apr 21 '14 at 21:30

1 Answers1

1
SELECT CONCAT('ALTER TABLE ', table_name,
              ' ADD FOREIGN KEY (', constraint_name, ')',
              ' REFERENCES ', referenced_table_name,
              '(', referenced_column_name, ');') AS alter_cmd
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL;
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • This is tremendous. Oen other thing ... if there wsa an "ON DELETE CASCADE" part of the constraint, how do I find that? – Dave Apr 22 '14 at 14:04
  • It's in the `information_schema.referential_constraints` table. https://dev.mysql.com/doc/refman/5.1/en/referential-constraints-table.html – Barmar Apr 22 '14 at 16:23
  • You're missing a closing parenthesis. I'm unable to edit, there is a 6 character minimum change – mrwaim Jun 20 '15 at 22:19