创建外键时出错
尝试添加外键约束时出现此错误：
#1005 - Can't create table './testtable/#sql-595_146.frm' (errno: 150)
当删除该项目时，我需要对共享项目 id 的所有图像执行 ON DELETE CASCADE。我的简化表结构如下：
CREATE TABLE IF NOT EXISTS `images` (
`image_id` mediumint(8) unsigned NOT NULL auto_increment,
`project_id` smallint(6) NOT NULL,
PRIMARY KEY (`image_id`),
KEY `project_id_ix` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;
CREATE TABLE IF NOT EXISTS `projects` (
`project_id` smallint(5) unsigned NOT NULL auto_increment,
PRIMARY KEY (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
现在，当我运行下面的查询来添加约束时，查询失败并出现上面发布的错误。有人可以帮忙吗？
ALTER TABLE `images` ADD CONSTRAINT `project_id_fk` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON DELETE CASCADE;
谢谢一百万！
I'm getting this error when trying to add a foreign key contraint:
#1005 - Can't create table './testtable/#sql-595_146.frm' (errno: 150)
I need to do an ON DELETE CASCADE for all images that share a project id when that project is deleted. My simplified table structure is as follows:
CREATE TABLE IF NOT EXISTS `images` (
`image_id` mediumint(8) unsigned NOT NULL auto_increment,
`project_id` smallint(6) NOT NULL,
PRIMARY KEY (`image_id`),
KEY `project_id_ix` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;
CREATE TABLE IF NOT EXISTS `projects` (
`project_id` smallint(5) unsigned NOT NULL auto_increment,
PRIMARY KEY (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
Now, When i run the query below to add the constraint, the query fails with the error posted above. Could anyone help?
ALTER TABLE `images` ADD CONSTRAINT `project_id_fk` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON DELETE CASCADE;
Thanks a million!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
将
project_id列更改为具有相同大小的smallint(6)（或 5）。您还需要对它们进行签名或未签名。...来自 mysql 网站它说：
Change the
project_idcolumns to have the same sizesmallint(6)(or 5). You also need to make them both signed or unsigned....from the mysql website it says:
我认为数据类型需要匹配。一张表中的project_id 为smallint(6)，另一张表中的project_id 为smallint(5)。
I thought the datatype needed to match. project_id is smallint(6) in one table and smallint(5) in the other.
project_id 不应该都是
smallint(6)吗？Shouldn't the project_id's both be
smallint(6)?