Bug #17448 create table like is missing all constraint definitions

Description:
create table kris.sitetree like foris_web.sitetree

creates a table with all column definitions and key definitions, but all constraint definitions are missing.

How to repeat:
root@localhost [kris]> select version();
 +----------------+
 | version() |
 +----------------+
 | 5.0.18-max-log |
 +----------------+
 1 row in set (0.00 sec)
 root@localhost [kris]> show create table foris_web.sitetree\G
 *************************** 1. row ***************************
 Table: sitetree
 Create Table: CREATE TABLE `sitetree` (
 `id` bigint(10) NOT NULL,
 `domain_id` bigint(10) default NULL,
 `parent` bigint(10) default NULL,
 `site_id` bigint(10) default NULL,
 `text_id` bigint(10) default NULL,
 `page_id` bigint(10) default NULL,
 `sortorder` bigint(10) default '0',
 `openmode` bigint(10) default NULL,
 `owner_id` bigint(10) default NULL,
 `enteredby` bigint(10) default NULL,
 `dateentered` datetime default NULL,
 `changedby` bigint(10) default NULL,
 `datechanged` datetime default NULL,
 `linkedsite_id` bigint(10) default NULL,
 `link_id` bigint(10) default NULL,
 `linkedsitetree_id` bigint(10) default NULL,
 `linktype` bigint(10) default NULL,
 `shortcut` varchar(30) collate utf8_bin default NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_sitetree_shortcut` (`shortcut`),
 KEY `idx_sitetree_parent` (`parent`),
 KEY `idx_sitetree_site_id` (`site_id`),
 KEY `idx_text_id` (`text_id`),
 KEY `fk_sitetree_changedby` (`changedby`),
 KEY `fk_sitetree_domain` (`domain_id`),
 KEY `fk_sitetree_enteredby` (`enteredby`),
 KEY `fk_sitetree_linkedsite` (`linkedsite_id`),
 KEY `fk_sitetree_page` (`page_id`),
 KEY `fk_sitetree_sitetree` (`linkedsitetree_id`),
 CONSTRAINT `fk_sitetree_changedby` FOREIGN KEY (`changedby`) REFERENCES `foris_adm`.`acct` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_sitetree_domain` FOREIGN KEY (`domain_id`) REFERENCES `foris_adm`.`domain` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_sitetree_enteredby` FOREIGN KEY (`enteredby`) REFERENCES `foris_adm`.`acct` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_sitetree_linkedsite` FOREIGN KEY (`linkedsite_id`) REFERENCES `site` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_sitetree_page` FOREIGN KEY (`page_id`) REFERENCES `page` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_sitetree_parent` FOREIGN KEY (`parent`) REFERENCES `sitetree` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_sitetree_site` FOREIGN KEY (`site_id`) REFERENCES `site` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_sitetree_sitetree` FOREIGN KEY (`linkedsitetree_id`) REFERENCES `sitetree` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
 1 row in set (0.00 sec)
 
 root@localhost [kris]> create table kris.sitetree like foris_web.sitetree;
 Query OK, 0 rows affected (0.17 sec)
 
 root@localhost [kris]> show create table kris.sitetree\G
 *************************** 1. row ***************************
 Table: sitetree
 Create Table: CREATE TABLE `sitetree` (
 `id` bigint(10) NOT NULL,
 `domain_id` bigint(10) default NULL,
 `parent` bigint(10) default NULL,
 `site_id` bigint(10) default NULL,
 `text_id` bigint(10) default NULL,
 `page_id` bigint(10) default NULL,
 `sortorder` bigint(10) default '0',
 `openmode` bigint(10) default NULL,
 `owner_id` bigint(10) default NULL,
 `enteredby` bigint(10) default NULL,
 `dateentered` datetime default NULL,
 `changedby` bigint(10) default NULL,
 `datechanged` datetime default NULL,
 `linkedsite_id` bigint(10) default NULL,
 `link_id` bigint(10) default NULL,
 `linkedsitetree_id` bigint(10) default NULL,
 `linktype` bigint(10) default NULL,
 `shortcut` varchar(30) collate utf8_bin default NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_sitetree_shortcut` (`shortcut`),
 KEY `idx_sitetree_parent` (`parent`),
 KEY `idx_sitetree_site_id` (`site_id`),
 KEY `idx_text_id` (`text_id`),
 KEY `fk_sitetree_changedby` (`changedby`),
 KEY `fk_sitetree_domain` (`domain_id`),
 KEY `fk_sitetree_enteredby` (`enteredby`),
 KEY `fk_sitetree_linkedsite` (`linkedsite_id`),
 KEY `fk_sitetree_page` (`page_id`),
 KEY `fk_sitetree_sitetree` (`linkedsitetree_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
 1 row in set (0.00 sec)

Suggested fix:
While fixing this, I suggest you think about a solution that copies triggers as well, as they also do belong to the table and are often required for proper operation of the table.

12:21  sbester> Isotopp: "CREATE TABLE ... LIKE does not preserve any DATA
                DIRECTORY or INDEX DIRECTORY table options that were specified
                for the original table, or any foreign key definitions."
12:22  Isotopp> sbester: ok, downgrade bug to feature request. :)

This is now a feature request, although for me personally, a pretty important one.

12:22  Isotopp> sbester: triggers are also not copied. :(
12:23  sbester> Isotopp: you want to sponsor such a feature ? :)
12:23  Isotopp> sponsor as in "make a case that it is useful"? yes. pay for it? no.
12:23  Isotopp> i need create table like all the time,
12:23  Isotopp> together with insert select limit to create test cases.
12:24  Isotopp> this absolutely must work for testing at the site to work. just like mysqldump must work in all cases.
12:24  Valeriy> Isotopp: do you know any other RDBMS that has this feature?
12:24  Isotopp> bugs in these two utilities are directly sabotaging my work.
12:24  Isotopp> Valeriy: no. that's why i am using mysql.
12:25  sbester> seems logical it should work. Else just another partial feature..
12:25  Isotopp> Valeriy: starting from day one, i have loved the command line editor in mysql, and still do not use any gui tool, and mysqldump --no-data and create table like/insert select.

Thank you for a problem report. According to the manual (http://dev.mysql.com/doc/refman/5.0/en/create-table.html):

"CREATE TABLE ... LIKE does not preserve any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table, or any foreign key definitions."

So, it is a documented limitation. I'll mark this report as a verified feature request. I hope, this feature ("deep copy" of table's structure) will be eventually added, if it is really needed for customers. Although, I do not know any other RDBMS that supports it directly.