MySQL Bugs: #34932: CREATE TABLE xyz like zyx

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

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.

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.

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.
"