尝试 INSERT 时 MySQL 和外键冲突
我正在写《Agile Yii》一书。
无论如何,我正在尝试执行此命令:
INSERT INTO tbl_project_user_assignment (project_id, user_id) values ('1','1'), ('1','2');
我收到此错误:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`trackstar_dev`.`tbl_project_user_assignment`, CONSTRAINT `FK_project_user` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE)
所以..我想让我们看看 tbl_project 表是否有 project_id=1。快速执行 SELECT * FROM tbl_project;并且该项目存在。
好的,那么我们只检查用户,SELECT * FROM tbl_user;是的,ID 为 1 和 2 的 2 个用户。
我做错了什么?有错别字吗?敏捷 yii 书有几个错别字,但它们并不那么严重,而且太新了,所以没有勘误表报告(已经检查过)。
这是源代码中的数据库架构:
-- Disable foreign keys
SET FOREIGN_KEY_CHECKS = 0 ;
-- Create tables section -------------------------------------------------
-- Table tbl_project
CREATE TABLE IF NOT EXISTS `tbl_project` (
`id` INTEGER NOT NULL auto_increment,
`name` varchar(128) NOT NULL,
`description` text NOT NULL,
`create_time` DATETIME default NULL,
`create_user_id` INTEGER default NULL,
`update_time` DATETIME default NULL,
`update_user_id` INTEGER default NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB
;
-- DROP TABLE IF EXISTS `tbl_issue` ;
CREATE TABLE IF NOT EXISTS `tbl_issue`
(
`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(256) NOT NULL,
`description` varchar(2000),
`project_id` INTEGER,
`type_id` INTEGER,
`status_id` INTEGER,
`owner_id` INTEGER,
`requester_id` INTEGER,
`create_time` DATETIME,
`create_user_id` INTEGER,
`update_time` DATETIME,
`update_user_id` INTEGER
) ENGINE = InnoDB
;
-- DROP TABLE IF EXISTS `tbl_user` ;
-- Table User
CREATE TABLE IF NOT EXISTS `tbl_user`
(
`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
`email` Varchar(256) NOT NULL,
`username` Varchar(256),
`password` Varchar(256),
`last_login_time` Datetime,
`create_time` DATETIME,
`create_user_id` INTEGER,
`update_time` DATETIME,
`update_user_id` INTEGER
) ENGINE = InnoDB
;
-- DROP TABLE IF EXISTS `tbl_project_user_assignment` ;
-- Table User
CREATE TABLE IF NOT EXISTS `tbl_project_user_assignment`
(
`project_id` Int(11) NOT NULL,
`user_id` Int(11) NOT NULL,
`create_time` DATETIME,
`create_user_id` INTEGER,
`update_time` DATETIME,
`update_user_id` INTEGER,
PRIMARY KEY (`project_id`,`user_id`)
) ENGINE = InnoDB
;
-- The Relationships
ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_project` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_owner` FOREIGN KEY (`owner_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_requester` FOREIGN KEY (`requester_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `tbl_project_user_assignment` ADD CONSTRAINT `FK_project_user` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `tbl_project_user_assignment` ADD CONSTRAINT `FK_user_project` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
-- Insert some seed data so we can just begin using the database
INSERT INTO `tbl_user`
(`email`, `username`, `password`)
VALUES
('[email protected]','Test_User_One', MD5('test1')),
('[email protected]','Test_User_Two', MD5('test2'))
;
-- Enable foreign keys
SET FOREIGN_KEY_CHECKS = 1 ;
无论如何,提前致谢!
编辑: 澄清该项目确实存在^^。
mysql>从 tbl_project 中选择 id,name;
+----+--------------------+
|编号 |名称 |
+----+--------------------+
| 6 |项目1 |
| 1 |僵尸计划1 |
+----+--------------------+
2 行一组(0.00 秒)
I'm doing the Agile Yii book.
Anyway, I'm trying to execute this command:
INSERT INTO tbl_project_user_assignment (project_id, user_id) values ('1','1'), ('1','2');
And I get this error:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`trackstar_dev`.`tbl_project_user_assignment`, CONSTRAINT `FK_project_user` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE)
So.. I figure let's see if tbl_project table have project_id=1. Did a quick SELECT * FROM tbl_project; and the project exist.
Ok then let's just check the user, SELECT * FROM tbl_user; Yup 2 user with id 1 and 2.
What am I doing wrong? Is there a typo? The agile yii book have several typos but they're not as serious and it's too new so there's no errata reported (checked already).
Here's the database schema from the source code:
-- Disable foreign keys
SET FOREIGN_KEY_CHECKS = 0 ;
-- Create tables section -------------------------------------------------
-- Table tbl_project
CREATE TABLE IF NOT EXISTS `tbl_project` (
`id` INTEGER NOT NULL auto_increment,
`name` varchar(128) NOT NULL,
`description` text NOT NULL,
`create_time` DATETIME default NULL,
`create_user_id` INTEGER default NULL,
`update_time` DATETIME default NULL,
`update_user_id` INTEGER default NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB
;
-- DROP TABLE IF EXISTS `tbl_issue` ;
CREATE TABLE IF NOT EXISTS `tbl_issue`
(
`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(256) NOT NULL,
`description` varchar(2000),
`project_id` INTEGER,
`type_id` INTEGER,
`status_id` INTEGER,
`owner_id` INTEGER,
`requester_id` INTEGER,
`create_time` DATETIME,
`create_user_id` INTEGER,
`update_time` DATETIME,
`update_user_id` INTEGER
) ENGINE = InnoDB
;
-- DROP TABLE IF EXISTS `tbl_user` ;
-- Table User
CREATE TABLE IF NOT EXISTS `tbl_user`
(
`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
`email` Varchar(256) NOT NULL,
`username` Varchar(256),
`password` Varchar(256),
`last_login_time` Datetime,
`create_time` DATETIME,
`create_user_id` INTEGER,
`update_time` DATETIME,
`update_user_id` INTEGER
) ENGINE = InnoDB
;
-- DROP TABLE IF EXISTS `tbl_project_user_assignment` ;
-- Table User
CREATE TABLE IF NOT EXISTS `tbl_project_user_assignment`
(
`project_id` Int(11) NOT NULL,
`user_id` Int(11) NOT NULL,
`create_time` DATETIME,
`create_user_id` INTEGER,
`update_time` DATETIME,
`update_user_id` INTEGER,
PRIMARY KEY (`project_id`,`user_id`)
) ENGINE = InnoDB
;
-- The Relationships
ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_project` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_owner` FOREIGN KEY (`owner_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_requester` FOREIGN KEY (`requester_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `tbl_project_user_assignment` ADD CONSTRAINT `FK_project_user` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `tbl_project_user_assignment` ADD CONSTRAINT `FK_user_project` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
-- Insert some seed data so we can just begin using the database
INSERT INTO `tbl_user`
(`email`, `username`, `password`)
VALUES
('[email protected]','Test_User_One', MD5('test1')),
('[email protected]','Test_User_Two', MD5('test2'))
;
-- Enable foreign keys
SET FOREIGN_KEY_CHECKS = 1 ;
Anyway, thanks in advance!
EDIT:
Clarification that the project does indeed exist ^^.
mysql> select id,name from tbl_project;
+----+-------------------+
| id | name |
+----+-------------------+
| 6 | Project 1 |
| 1 | project zombied 1 |
+----+-------------------+
2 rows in set (0.00 sec)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这是您遇到的一个奇怪的问题,也是一个奇怪的解决方案。据我所知,INTEGER、INT 或 INT(XX)(其中 XX 是某个数字)之间没有内部差异,它们都是相同的数据类型,具有相同的字节存储分配和最小/最大范围。这不应该在 MySQL 评估某些 fk 关系的类型不匹配时发挥作用。我的 MySQL 版本/配置 (5.1.49) 不会引发您在一个表中使用 INT(11) 而在另一个表中使用 INTEGER 时遇到的相同约束违规。我想知道这是否与您的配置更相关,或者您是否正在使用其他外部数据库工具。
人们可以在这里阅读有关 MySQL 数据类型内部的更多信息:
http:// /dev.mysql.com/doc/refman/5.0/en/numeric-types.html
此页面特别感兴趣的
This is a strange issue you have encountered, as well as an odd fix for it. As far as I am aware, there is no internal difference between INTEGER, INT, OR INT(XX) (where XX is some number) They are all the same datatype with the same byte storage allocation and min/max range. This should not play a role in MySQL evaluation of type mismatch for some fk relationships. My version/configuration of MySQL (5.1.49) does not throw the same constraint violation you are experiencing when given using INT(11) in one table and INTEGER in another. I wonder if this is somehow more related to your configuration or if you are using other external DB tools.
One can read more about the internals of MySQL datatype here:
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
of particular interest on this page:
tbl_project_user_assignment 中的project_id 和user_id 的类型为INT(11) 而不是INTEGER。我倾向于认为 INTEGER 是 4 个字节,而 INT(11) 会是 8 个字节。
正如上面评论的 INTEGER 解决了这个问题。
The project_id and user_id in tbl_project_user_assignment are typed as INT(11) rather than INTEGER. I'm inclined to think that INTEGER is 4 BYTES and INT(11) would go to 8 BYTES.
As commented above INTEGER fixes the problem.