Bug #34932 CREATE TABLE xyz like zyx
Submitted: 28 Feb 2008 15:22 Modified: 7 Mar 2008 16:55
Reporter: Frank Mussmann Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.0, 5.1, 6.0 OS:Linux
Assigned to: CPU Architecture:Any
Tags: create, foreign key, like, table
Triage: Needs Triage: D5 (Feature request)

[28 Feb 2008 15:22] Frank Mussmann
Description:
Hi,
when I copy a table (let's call it B) which has a foreign key to Table A and I want to make a quick copy of table B (including the foreign Key) I have to work around. Now I do these steps:
show create table B\G
>CREATE TABLE B ....... (WITH FOREIGN KEY)
I take the output, modify the table name and start it over.
Thus there is the possibility to copy the definition of the table with create table C like B, I'd like to have the foreign keys copied as well.

How to repeat:
- create table t1 (a INT NOT NULL,b INT not null, PRIMARY KEY (`a`)) engine=InnoDB;
- create table t2 (a INT NOT NULL, b FLOAT not null, CONSTRAINT `FK_t2_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE) Engine=InnoDB;
- create table t3 like t2;
- show create table t3\G
- show create table t2\G
- insert into t1 values(1,1);
- insert into t3 values(3,3); <-- THIS SHOULDN'T work . :-\
- insert into t2 values(3,3); <--- ERROR 1452 Foreign key !!!

Suggested fix:
Extend the create table like so that everything of the structure is copied.
[7 Mar 2008 16:55] Susanne Ebrecht
Many thanks for writing a feature request.

I think to support this is a good idea.

Something like:
CREATE TABLE tab LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS } ]

would make sense.
[15 Feb 2009 18:33] Peter Gulutzan
For WL#148 "Foreign Keys (all storage engines)" the decision was:
"
CREATE TABLE ... LIKE
---------------------

"CREATE TABLE t1 LIKE t2" does not copy t2's foreign keys,
so no special code is needed for this.
"