MySQL InnoDB表之间的外键不工作...为什么？

本文介绍了MySQL InnoDB表之间的外键不工作...为什么？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 （b）b $ b specie（MyIsam） image（InnoDB） specie_map（InnoDB） specie_map表应该映射一个图片到一个实体，因此有以下列： specie_id image_id 两者都是int 11，就像物理表和图像表的id列一样。我知道我不能在specie_id和specie => id之间创建一个外键，因为这个specie表是一个MyIsam表。不过，我希望能够在image_id和image => id之间创建一个外键。 我可以创建外键并保存它，但是，我已经关联的CASCADE操作不起作用。当我删除一个图像时，它不会删除与之关联的specie_map条目。我期望这个工作，因为这个外键在InnoDB表之间。这两个列是索引和相同的数据类型。 这是MySQL的限制，还是我做错了什么？ 更新：根据要求提供表格定义。我剪掉了不重要的列： - ------------------ ---------- - `image`的表结构 - ---------------------- ------ DROP TABLE IF EXISTS`image`; CREATE TABLE`image`（` id` int（11）NOT NULL auto_increment，$ b $``guid` char（36）default NULL，`title` varchar（255） NOT NULL，`description` text，`user_id` int（11）NOT NULL，`item_id` int（11）default NULL，`date_uploaded` timestamp NOT NULL默认'0000-00-00 00:00:00'，`date_created`时间戳NOT NULL默认值'0000-00-00 00:00:00'，`date_modified` timestamp NOT NULL default' （'softdeleted'，'tobedeleted'，'active'）默认'active'， PRIMARY KEY（` （````````````，`````````，`````````$ b $`` CONSTRAINT`image_mod_by` FOREIGN KEY（`moderated_by`）REFERENCES`user`（`id`）ON DELETE NO ACTION ON UPDATE NO ACTION， CONSTRAINT`image_user` FOREIGN KEY（`user_id`）REFERENCES`user`（` ID`）ON删除没有行动上更新没有行动）ENGINE = InnoDB默认CHARSET = utf8 COMMENT ='存储图像数据（不是文件数据）'; - ---------------------------- - 表结构对于`specie` - ---------------------------- 如果出现`DROP TABLE'specie`; CREATE TABLE`specie`（` id` int（11）NOT NULL auto_increment，`name`varchar（256）NOT NULL，`commonname` varchar（256） NOT NULL， PRIMARY KEY（`id`））ENGINE = MyISAM AUTO_INCREMENT = 22 DEFAULT CHARSET = utf8 ROW_FORMAT = DYNAMIC; - ---------------------------- - 表结构for`specie_map` - ---------------------------- DROP TABLE如果存在`specie_map`; CREATE TABLE`specie_map`（` id` int（11）NOT NULL auto_increment，` image_id` int（11）NOT NULL，`specie_id` int（11） NOT NULL，`user_id` int（11）NOT NULL，`karma` int（11）NOT NULL，`date_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP，主键（`id`），键`image_id`（`image_id`），键`specie_id`（`specie_id`）， CONSTRAINT`specie_map_ibfk_1` FOREIGN KEY（`image_id ``）参考`image`（`id`）ON DELETE CASCADE ON UPDATE NO ACTION ）ENGINE = InnoDB DEFAULT CHARSET = utf8; 解决方案当你试图创建这些表和外键。 innodb表之间的外键工作正常，但仍有一些事情要注意： $ b $ 0这两个表必须是InnoDB。这已经被其他海报所强调，这可能是你的问题的直接原因。 $ b $ 1）引用列的数据类型（组成外部键）和他们各自的引用列应该是相同的。例如，您不能在INT UNSIGNED列上创建一个外键约束到普通的INT列。 2）如果外键是作为表DDL，一定要将外键定义在约束部分，即在所有列定义之下。例如： CREATE TABLE parent（ id int unsigned PRIMARY KEY ）; $ b $ CREATE TABLE child（ parent_id int unsigned ，foreign key（parent_id）引用parent（id））; 可以工作，但是： ）parent_id int unsigned 外键引用parent（id））; 不会。它会失败默默无闻，因为即使在InnoDB创建表之前，MySQL的解析器也会忽略这些类型的约束定义（愚蠢的，但事实就是这样）。 $ b $ 3必须有所有引用列的索引。通常被引用的列会一起组成一个主键或一个唯一的约束，但是在定义这个外键之前定义这个是你的工作。 最后一点建议：如果你认为你的DDL是可以的，但是执行时仍然会出错，例如： 错误1005（HY000）：无法创建表'test.child'（errno：150）警告（代码150）：使用外键约束创建表'test / child'失败。在引用的表中没有索引，其中引用的列显示为第一列。 错误（代码1005）：无法创建表'test.child'（errno：150） 然后这些错误可能仍然不能揭示错误的真实性质（又是愚蠢的，但事实就是这样）。为了说明这一点，在尝试创建外键后立即运行此命令： SHOW ENGINE INNODB STATUS; 这会给你一些状态信息，其中一个部分看起来像这样： ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 120122 11:38:28表测试/ child：外键（parent_id）引用parent（id））：在引用的表中找不到索引，其中引用列显示为第一列或列类型$ b $在表中的b和被引用的表不匹配约束。 请注意，在> = InnoDB-4.1.12创建的表中，ENUM和SET的内部存储类型发生了变化，旧表中的这样的列不能被新的表。 正确的外键定义见http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html 。 正如你所看到的，这给出了更多的信息，揭示了真正的问题，即列类型在表中和被引用的表不匹配的约束 所以，请发布您的实际的DDL，我敢肯定有一个问题在那里。 p> I have the following tables:specie (MyIsam)image (InnoDB)specie_map (InnoDB) The specie_map table should map an image to a specie, and therefore has the following columns:specie_idimage_idBoth are int 11, just like the id columns of the specie and image tables. I know I can't create a foreign key between specie_id and specie=>id, since the specie table is a MyIsam table. However, I would expect it to be possible to create a foreign key between image_id and image=>id. I can create that foreign key and it will save it, however, the CASCADE action I have associated with it does not work. When I delete an image, it does not delete the specie_map entry that is associated with it. I would expect this to work, as this foreign key is between InnoDB tables. Both columns are indexed and of the same data type.Is this a limitation of MySQL, or am I doing something else wrong?Update: as requested hereby the table definitions. I have snipped unimportant columns:-- ------------------------------ Table structure for `image`-- ----------------------------DROP TABLE IF EXISTS `image`;CREATE TABLE `image` ( `id` int(11) NOT NULL auto_increment, `guid` char(36) default NULL, `title` varchar(255) NOT NULL, `description` text, `user_id` int(11) NOT NULL, `item_id` int(11) default NULL, `date_uploaded` timestamp NOT NULL default '0000-00-00 00:00:00', `date_created` timestamp NOT NULL default '0000-00-00 00:00:00', `date_modified` timestamp NOT NULL default '0000-00-00 00:00:00', `status` enum('softdeleted','tobedeleted','active') default 'active', PRIMARY KEY (`id`), KEY `image_user` (`user_id`), KEY `image_item` (`item_id`), KEY `image_mod_by` (`moderated_by`), CONSTRAINT `image_mod_by` FOREIGN KEY (`moderated_by`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `image_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='stores image data (not file data)';-- ------------------------------ Table structure for `specie`-- ----------------------------DROP TABLE IF EXISTS `specie`;CREATE TABLE `specie` ( `id` int(11) NOT NULL auto_increment, `name` varchar(256) NOT NULL, `commonname` varchar(256) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;-- ------------------------------ Table structure for `specie_map`-- ----------------------------DROP TABLE IF EXISTS `specie_map`;CREATE TABLE `specie_map` ( `id` int(11) NOT NULL auto_increment, `image_id` int(11) NOT NULL, `specie_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `karma` int(11) NOT NULL, `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `image_id` (`image_id`), KEY `specie_id` (`specie_id`), CONSTRAINT `specie_map_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `image` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=utf8; 解决方案 I think you should post the exact DDL statements you used when you attempted to create these tables and the foreign key. Foreign keys between innodb tables work fine, but there are still a few things to look out for:0) Both tables must be InnoDB. This was already highlighted by the other posters and this is probably the immediate cause of your problem.1) the data type of the referencing columns (those that make up the foreign key) and their respective referenced columns should be the same. For example, you can't create a foreign key constrain on an INT UNSIGNED column to a plain INT column.2) if the foreign key is created as part of the table DDL, be sure to put the foreign key definition in the constraints section, that is, below all column definitions. For example:CREATE TABLE parent ( id int unsigned PRIMARY KEY);CREATE TABLE child ( parent_id int unsigned, foreign key (parent_id) references parent (id));will work but this:CREATE TABLE child ( parent_id int unsigned foreign key references parent (id));won't. It will fail silently because MySQL's parser ignores these types of constraint definitions even before InnoDB gets to create the table (silly, but that's how it is) 3) There must be an index over all the referenced columns. Usually the referenced columns will together make up a primary key or a unique constraint anyway, but it is your job to define this before defining the foreign key.Final word of advice: if you think your DDL is ok but you still get an error when you execute it, for example like this:ERROR 1005 (HY000): Can't create table 'test.child' (errno: 150)Warning (Code 150): Create table 'test/child' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.Error (Code 1005): Can't create table 'test.child' (errno: 150)Then these errors may still not reveal the true nature of the error (silly again, but that's how it is). To shed more light on it, run this command immediately after your attempt to create the foreign key:SHOW ENGINE INNODB STATUS;This will give you a bunch of status info, and one section there looks like this:------------------------LATEST FOREIGN KEY ERROR------------------------120122 11:38:28 Error in foreign key constraint of table test/child:foreign key (parent_id) references parent (id) ):Cannot find an index in the referenced table where thereferenced columns appear as the first columns, or column typesin the table and the referenced table do not match for constraint.Note that the internal storage type of ENUM and SET changed intables created with >= InnoDB-4.1.12, and such columns in old tablescannot be referenced by such columns in new tables.See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.htmlfor correct foreign key definition.As you can see, this gives a bit more information and reveals the true problem, namely "column types in the table and the referenced table do not match for constraint" So please, post your actual DDL, I'm sure there is a problem in there somewhere. 这篇关于MySQL InnoDB表之间的外键不工作...为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！