Creating a MySQL table with two foreign keys in a cross-reference table

Question

This probably comes from some lack of knowledge or plain stupidity, but I would like to understand how to create foreign keys in a cross reference table:

For example, I have two tables, item and image

CREATE TABLE `item` (
    `id` INT(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL,
    `description` MEDIUMTEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `image` (
    `id` INT(11) UNSIGNED AUTO_INCREMENT,
    `file` VARCHAR(255) UNIQUE NOT NULL,
    `caption` VARCHAR(255),
    PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I want to reference any image to any item, so I create a cross-reference table like this:

CREATE TABLE `item_image` (
    `item` INT(11) UNSIGNED NOT NULL,#foreign key
    `img` INT(11) UNSIGNED NOT NULL,#foreign key
    PRIMARY KEY(`item`,`img`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

But the item_image table contains columns that are foreign keys, so I try to constrain them like this

CREATE TABLE `item_image` (
    `item` INT(11) UNSIGNED NOT NULL,
    `img` INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY(`item`,`img`),
    FOREIGN KEY(`item`)
        REFERENCES `item` (`id`) 
        ON DELETE CASCADE,
    FOREIGN KEY(`img`)
        REFERENCES `image` (`id`) 
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So that if the image or the item get removed, the database would remove the cross reference too.

I have read lots of resources on the web, and stackoverflow, and it is often suggested that there should be no problem in doing what I want Defining multiple foreign keys in one table to many tables

but MySQL gives me this generic error

Error Code: 1005. Can't create table 'test.item_image' (errno: 150)

I need some help to understand what exactly MySQL is complaining about.

Edit:

I execute the queries in MySQL Workbench 5.2.39 CE if that matters...

MySQL version: 5.5.16

Actually, I just executed those statements and it works perfectly... — Konerak, May 09 '12 at 08:10
@Konerak what the heck!? why do I keep getting these errors that no one else gets... — Timo Huovinen, May 09 '12 at 08:11
@Yuri: http://pastebin.com/twrVUZVe - what version MySQL are you using? — Konerak, May 09 '12 at 08:12
Are your tables empty? If not, you either want to make sure all the constraints are met, the table is made empty, or you set foreign-key-checks to off? — Nanne, May 09 '12 at 08:17
@Nanne tables are empty, `SELECT @@foreign_key_checks;` gives 1, so they are on. — Timo Huovinen, May 09 '12 at 08:27
@Konerak I found the error thanks to your pastebin and gordatron's sqlfiddle and Nanne made me realize the source of the problem. I was creating the item_image table before the image table... It's always something stupid like this that gets me. Let's see if I'm not the only one. — Timo Huovinen, May 09 '12 at 08:35
Should I edit the question and post the answer, or just delete the question? — Timo Huovinen, May 09 '12 at 08:38
@YuriKolovsky: Post as an answer to your own question - it may help others in the future. — eggyal, May 09 '12 at 08:48

score 3 · Accepted Answer · edited May 23 '17 at 11:56

3

I was creating the item_image table before the image table, and this caused me to get the MySQL error 150

I feel stupid now :)

the error can also be caused by other reasons, in which case you should check

MySQL Creating tables with Foreign Keys giving errno: 150

http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/

edited May 23 '17 at 11:56

Community

1
1

answered May 09 '12 at 09:10

Timo Huovinen

53,325
33
152
143

Beside that you solved the issue, **the design is wrong**. It is not a relation of *Many to Many*, but a *1 to Many* (1 art. Many images). So you don't need to use a cross-reference table, you should store a foreign key in the `image` table. – Lucio Sep 01 '13 at 22:01
@Lucio some articles re-use images from other articles, so the design is not wrong. – Timo Huovinen Sep 02 '13 at 10:22

Creating a MySQL table with two foreign keys in a cross-reference table

1 Answers1