[MDEV-15868] 'CREATE TABLE t3 LIKE t2' drops foreign key constraint Created: 2018-04-14  Updated: 2018-04-15  Resolved: 2018-04-15

Status: Closed
Project: MariaDB Server
Component/s: Documentation
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: T.Peter Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: create_table, foreign-keys
Environment:

ubuntu 16.04 server


 Description   

Simple Example:

create table t1(f1 int ,primary key(f1))engine=innodb;
 
create table t2(f2 int,f3 int, key t(f2),key t1(f2,f3),foreign key(f2) references t1(f1))engine=innodb;
 
create table t3 LIKE t2;
 
SHOW CREATE TABLE t2;
 
SHOW CREATE TABLE t3;

`SHOW CREATE TABLE t2;` is correct: 

CREATE TABLE `t2` (
 
  `f2` int(11) DEFAULT NULL,
 
  `f3` int(11) DEFAULT NULL,
 
  KEY `t` (`f2`),
 
  KEY `t1` (`f2`,`f3`),
 
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

'SHOW CREATE TABLE t3;' shows that t3 is missing the foreign key constraint:

CREATE TABLE `t3` (
 
  `f2` int(11) DEFAULT NULL,
 
  `f3` int(11) DEFAULT NULL,
 
  KEY `t` (`f2`),
 
  KEY `t1` (`f2`,`f3`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci



 Comments   
Comment by T.Peter [ 2018-04-14 ]

Strange things happen. Looks like this has been the case all along!?
At least states the mysql-Doc
this:
> 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.

Comment by Elena Stepanova [ 2018-04-14 ]

Right, as you found out yourself, it's documented and thus expected behavior.
It needs to be documented in MariaDB KB as well, though.

Comment by Ian Gilfillan [ 2018-04-15 ]

Documented on https://mariadb.com/kb/en/library/create-table/

Generated at Thu Feb 08 08:24:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.