Alex Rivera | Logout

mysql: Copy table structure including foreign keys

Asked 2010-09-20T21:20:07.637
25

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?

mysql
Edit
Report

1 Answer

6

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;
answered 2010-09-20T23:22:49.907

Your Answer