mysql truncate 失败,尝试截断表时出现MySQL错误

I'm having problems to truncate a table on the MySQL Server 5.5.

The table I'm trying to truncate has a column that serves as a foreign key in another table.

The CREATE TABLE of both tables involved is as it follows:

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 the constraints are both set to DELETE and UPDATE ON CASCADE.

When I try to TRUNCATE the table:

TRUNCATE TABLE ``.`tbluser`;

I receive the following error message:

Cannot truncate a table referenced in a foreign key constraint

(``.`tblpost`,

CONSTRAINT `FK_tblpost_updateUserId`

FOREIGN KEY (`updateUserId`)

REFERENCES ``.`tbluser` (`id`))

In addition to this information, there is the fact that when the action above is attempted on a MySQL Server 5.1, it works!

Does anyone have an idea of why this is happening?

解决方案

Check here . That makes sense that TRUNCATE TABLE raises an error in such cases; the bad thing that it's not documented.