Foreign key error on the MySQL CREATE TABLE statement (err: 150)


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

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

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.