Foreign Key Error on MySQL CREATE TABLE statement

Question

I feel I've tried everything possible on a very simple pair of create table statements.

The types match, I tried using ENGINE = InnoDB, etc and am stumped why I'm getting the foreign key error.

I've been away from SQL for some time, so this is probably an easy one.

mysql> CREATE TABLE foo_ent(yyy_no VARCHAR(80),
    -> zoo VARCHAR(80),
    -> PRIMARY KEY (yyy_no));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE cat_ent(some_item INTEGER,
    -> yyy_no VARCHAR(80),
    -> apple DECIMAL(6,2),
    -> PRIMARY KEY (some_item),
    -> FOREIGN KEY (yyy_no) REFERENCES foo_ent);
ERROR 1005 (HY000): Can't create table 'test.cat_ent' (errno: 15
0)

Sorry about the poor variable names, safe to over-write company stuff.

Answer 1

You don't reference to a field, only a table, which is incorrect.

...
foreign key (yyy_no) references foo_ent(yyy_no)

And according to your error number, the MySQL documentation also states;

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Answer 2

Consider rewriting your FOREIGN KEY declaration to explicitly list the column in foo_ent that you want to key on:

CREATE TABLE cat_ent(some_item INTEGER, yyy_no VARCHAR(80), apple DECIMAL(6,2), PRIMARY KEY (some_item), FOREIGN KEY (yyy_no) REFERENCES foo_ent(yyy_no)) ENGINE=InnoDB;

works for me.

Answer 3

You should provide the name of the referenced column explicitly:

CREATE TABLE cat_ent
        (
        some_item INTEGER NOT NULL,
        yyy_non VARCHAR(80),
        apple DECIMAL(6,2),
        PRIMARY KEY (some_item),
        FOREIGN KEY (yyy_non) REFERENCES foo_ent(yyy_no)
        );