Foreign key between MySQL InnoDB tables not workin

Question

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_id
image_id

Both 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;

Answer 1

Foreign keys works only with InnoDb in mysql. MyISAM doesn't support them (the statements are ignored).

And is there any reason why you mix multiple DB engines?

Answer 2

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 the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for 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.