What happens to foreign key constraints in create...select statement?

Question

Just wondering what happens to the foreign key constrains of a source table, when it is replicated using CREATE...SELECT statement. Will the referential constraints be copied or ignored?

Here is an example:

CREATE TABLE foo(a int, b int, PRIMARY KEY(a));
CREATE TABLE source(id int, a int, PRIMARY KEY(id), FOREIGN KEY(a) REFERENCES foo(a));

CREATE TABLE target SELECT * FROM source;

So, my question is if attribute a on target also points to foo(a)? If the answer is vendor specific, I'm looking the answer for MySQL.

Answer 1

No, it won't create the FK constraint in target. CREATE TABLE...SELECT just looks at the columns and data types of the SELECT result set, and that determines the definition of the new table.

I thought that using CREATE TABLE...LIKE could be a workaround for you, but I tested it on MySQL 5.5, and it also does not create the foreign keys.

CREATE TABLE target LIKE source;

I'm guessing because foreign keys are implemented in the InnoDB storage engine, not recognized by the storage-independent layer of MySQL, they probably don't show up in the storage-independent .FRM file.

This bug seems to confirm that CREATE TABLE...LIKE is implemented by simply copying the .FRM file: http://bugs.mysql.com/bug.php?id=35526

But the fix mentioned was put into the MySQL 6.1 branch, and that branch was killed long ago.

So for now, you'll have to declare the foreign keys as a separate step. I understand that in the future, the .FRM file is going away, so they'll have to change how CREATE TABLE...LIKE works. Anyway, that doesn't help you today.