Error when inserting multiple MySQL foreign keys

Question

When I try to insert the current table into my table in SQL I get an error (Products table):

CREATE TABLE parent(

    Barcode     INT(9),
    PRIMARY KEY (Barcode)
) ENGINE=INNODB;

CREATE TABLE SuppliedBy(

    Onr         CHAR(10),
    OrgNR       INT(10),
    Date        DATE            NOT NULL,           
    PRIMARY KEY (Onr),
    FOREIGN KEY (OrgNR) REFERENCES Supplier(OrgNR)
                          ON DELETE CASCADE
                          ON UPDATE CASCADE
) ENGINE=INNODB;

CREATE TABLE Products(

    Onr     CHAR(10),
    Barcode     INT(9),
    Quantity    INT(10)         DEFAULT 0
                        CHECK (Quantity >= 0),
    PRIMARY KEY (Onr, Barcode),
    FOREIGN KEY (Onr) REFERENCES SuppliedBy(SSN)
                                ON DELETE CASCADE
                                ON UPDATE CASCADE,
    FOREIGN KEY (Barcode) REFERENCES parent(Barcode)
                                ON DELETE CASCADE
                                ON UPDATE CASCADE
)ENGINE=INNODB;

I get the following message:

#1005 - Can't create table '.\db_project\#sql-b58_6d.frm' (errno: 150)

I'm sure it has to do with the several foreign keys in this relation, I searched around on the internet, but can't find the solution.

Please show us the table definitions of the tables referenced by the foreign keys as well. — Romain, May 09 '12 at 12:42
Post the `CREATE TABLE` for the related tables SuppliedBY and parent. If the column definition of the relations in this table doesn't match exactly, you'll get err 150. That is, `parent.Barcode` must also be `INT(9)` for example. — Michael Berkowski, May 09 '12 at 12:42
@Michael I believe it's also true if the other tables use engines that don't support indexes or foreign keys - although I'm not 100% sure. — Romain, May 09 '12 at 12:43

score 2 · Accepted Answer · answered May 09 '12 at 12:51

2

There is no column SuppliedBy.SSN.

FOREIGN KEY (Onr) REFERENCES SuppliedBy(SSN)

Perhaps you meant

FOREIGN KEY (Onr) REFERENCES SuppliedBy(Onr)
                            ON DELETE CASCADE
                            ON UPDATE CASCADE,

answered May 09 '12 at 12:51

Michael Berkowski

267,341
46
444
390

score 0 · Answer 2 · edited May 23 '17 at 10:34

0

I believe the issue is likely that one of the tables you're defining the FOREIGN KEYS to point to does not have an index foreign key field you're pointing to.

For FOREIGN KEY's to work, the field you're pointing to needs to have an index on it.

See Mysql. Can't create table errno 150

Also, check that you meet all the criteria for creating the key. The columns in both tables must:

Be the same datatype
Be the same size or length
Have indexes defined.

edited May 23 '17 at 10:34

Community

1
1

answered May 09 '12 at 12:50

Kevin Bedell

13,254
10
78
114

1

MySQL will create the index if none exists. You'll get Error 150 if you try to drop the index before dropping the foreign key constraint. – Romain May 09 '12 at 12:51
Not on the REFERENCED TABLE. It will not. You need indexes on both tables. I believe you misunderstood my answer. – Kevin Bedell May 09 '12 at 12:59
Adding a foreign key adds indexes on both sides. Although apparently it will not do it on its own if you inline the foreign key in the table creation... – Romain May 09 '12 at 13:02
Thanks for acknowledging that. Can pls remove the downvote? I believe this answers the question correctly. – Kevin Bedell May 09 '12 at 13:05
I removed my downvote right after acknowledging I was wrong ;) Because [that's how I roll](http://stackoverflow.com/users/223339/romain). – Romain May 09 '12 at 13:16

Error when inserting multiple MySQL foreign keys

2 Answers2