I'm having problems truncating a table on MySQL Server 5.5.
The table I'm trying to truncate has a column that serves as a foreign key in another table.
CREATE TABLE both tables involved looks like this:
CREATE TABLE `tbluser` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `name` varchar(255) NOT NULL, `creationDate` datetime NOT NULL, `creationUserId` int(11) NOT NULL, `updateDate` datetime NOT NULL, `updateUserId` int(11) NOT NULL, `lastAccess` datetime NOT NULL, `enabled` tinyint(1) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id_UNIQUE` (`id`), UNIQUE KEY `email_UNIQUE` (`email`), KEY `FK_tbluser_creationUserId` (`creationUserId`), KEY `FK_tbluser_updateUserId` (`updateUserId`), CONSTRAINT `FK_tbluser_updateUserId` FOREIGN KEY (`updateUserId`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_tbluser_creationUserId` FOREIGN KEY (`creationUserId`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; CREATE TABLE `tblpost` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `content` mediumtext NOT NULL, `creationDate` datetime NOT NULL DEFAULT '1901-01-01 00:00:00', `creationUserId` int(11) NOT NULL, `updateDate` datetime NOT NULL DEFAULT '1901-01-01 00:00:00', `updateUserId` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `FK_tblpost_creationUserId` (`creationUserId`), KEY `FK_tblpost_updateUserId` (`updateUserId`), CONSTRAINT `FK_tblpost_updateUserId` FOREIGN KEY (`updateUserId`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_tblpost_creationUserId` FOREIGN KEY (`creationUserId`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Please note that all restrictions are set as DELETE and UPDATE ON CASCADE .
When I try to execute a TRUNCATE table:
TRUNCATE TABLE `<databasename>`.`tbluser`;
The following error message appears:
Cannot truncate a table referenced in a foreign key constraint (`<databasename>`.`tblpost`, CONSTRAINT `FK_tblpost_updateUserId` FOREIGN KEY (`updateUserId`) REFERENCES `<databasename>`.`tbluser` (`id`))
In addition to this information, there is the fact that when the action described above is performed on MySQL Server 5.1, it works!
Does anyone have an idea why this is happening?