问题描述
我在截断MySQL Server 5.5上的表时遇到问题.
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.
所涉及的两个表的CREATE 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;
请注意,所有约束都设置为DELETE
和UPDATE ON CASCADE
.
Please note that all the constraints are both set to DELETE
and UPDATE ON CASCADE
.
当我尝试TRUNCATE
表格时:
TRUNCATE TABLE `<databasename>`.`tbluser`;
我收到以下错误消息:
Cannot truncate a table referenced in a foreign key constraint
(`<databasename>`.`tblpost`,
CONSTRAINT `FK_tblpost_updateUserId`
FOREIGN KEY (`updateUserId`)
REFERENCES `<databasename>`.`tbluser` (`id`))
除了此信息外,还有一个事实是,当在MySQL Server 5.1上尝试执行上述操作时,它会起作用!
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?
推荐答案
在此处.在这种情况下TRUNCATE TABLE
会引发错误,这是有道理的;不好的事情是没有记录.
Check here . That makes sense that TRUNCATE TABLE
raises an error in such cases; the bad thing that it's not documented.
这篇关于尝试截断表时出现MySQL错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!