Error When creating a foriegn key

Question

Hello,

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!

Answer 1

Change the project_id columns to have the same size smallint(6) (or 5). You also need to make them both signed or unsigned.

...from the mysql website it says:

Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

Answer 2

I thought the datatype needed to match. project_id is smallint(6) in one table and smallint(5) in the other.

Answer 3

Shouldn't the project_id's both be smallint(6)?