MySQL Bugs: #64648: Doesnt allow to Truncate Parent Table

Bug #64648	Doesnt allow to Truncate Parent Table
Submitted:	14 Mar 2012 12:24	Modified:	14 Mar 2012 14:43
Reporter:	Flavian C	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a Parent table is to be truncated it doesnt allow even though its child table don't have any data..

Mysql 5.5.19 Innodb

How to repeat:
Two Table 1 is the Parent Table and 2nd is the child of 1st Table.

Suppose both the table have data in it.
Truncate the Child table(2nd table) or delete its data entirely.
Now try to truncate the Parent table using Truncate table command it will throw an exception of foreign key constraint. But it will allow to use DELETE the data.

Please, send complete, repeatable test case. Just copy/paste all statements executed and error messages you get, for example.

create 2 table 

CREATE TABLE `parentTable` (
  `col1` varchar(10) NOT NULL,
  `col2` varchar(80) DEFAULT NULL,
  `col3` varchar(100) DEFAULT NULL,
  `col4` varchar(200) DEFAULT NULL,
  `version` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`col1`))ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
insert into `parentTable`(`col1`,`col2`,`col3`,`col4`) values ('xxx','aaa','bbb','ddd'),('yyy','aaa','ccc','eee');   
  
  CREATE TABLE `childTable` (
    `bol1` int(3) NOT NULL,
    `bol2` varchar(15) NOT NULL,
    `bol3` int(11) NOT NULL,
    `bol4` varchar(20) NOT NULL,
    `version` int(3) NOT NULL DEFAULT '0',
    PRIMARY KEY (`bol1`,`bol2`,`bol4`),
    KEY `FK_childTable` (`bol4`),
    CONSTRAINT `FK_childTable` FOREIGN KEY (`bol4`) REFERENCES `parentTable` (`col1`) ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=0;

insert into `childTable`(`bol1`,`bol2`,`bol3`,`bol4`) values ('1','sadd','111','xxx'),('2','sada','222','yyy'),('3','fwfc','333','xxx'); 

Now I need to truncate both the tables.

First I truncated the childTable and it works.

Then when I tried to truncate parentTable, it shows error. 

Cannot truncate a table referenced in a foreign key constraint (`childtable`, CONSTRAINT `FK_childTable` FOREIGN KEY (`bol4`) REFERENCES `parentTable` (`col1`))
(0 ms taken) 

Even though there isn’t any data in childTable, why I couldn’t truncate the parentTable?

This is a documented limitation, check http://dev.mysql.com/doc/refman/5.5/en/truncate-table.html:

"TRUNCATE TABLE fails for an InnoDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are allowed."

This is the case for a long time already. Check bug #54678 for the details why this was introduced.

>>This is the case for a long time already. Check bug #54678 for the details why this was

In 5.1.50 or 58

The ParentTable used to check if the data is present in childTable before it would drop and recreate the table when truncate command is issued.

eg if the data is present in the childTable referencing the parentTable 
truncate table would give foreign key constraint.
if data is truncated in childTable  and then you try truncate table on parentTable it would go through.