mysql: Copy table structure including foreign keys

Question

I know how to copy a table using create new_table like old_table, but that does not copy over the foreign key constraints as well. I can also do string manipulation on the result from show create table old_table (using regular expressions to replace the table name and the foreign key constraint names), but that seems error prone. Is there a better way to copy the structure of a table, including the foreign keys?

Answer 1

Possibly you could write a procedure that after the create table like prepares ALTER TABLE ... statements, based on information from:

SELECT *  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  WHERE TABLE_NAME LIKE '<table_name>'  AND TABLE_SCHEMA = '<db_name>' AND REFERENCED_TABLE_NAME IS NOT NULL;