尝试截断表时出现MySQL错误 [英] MySQL error when trying to truncate table

问题描述

我在截断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错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！