Dies ist die Fehlermeldung von show engine innodb status;
wenn ich versuche, die Tabelle zu erstellen:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
110628 16:56:07 Error in foreign key constraint of table test/menu_items:
foreign key(id_menu)
references menus(id)
on update cascade
on delete cascade,
foreign key(id_item)
references items(id)
on update cascade
on delete cascade,
primary key(id_menu, id_item)
) engine=InnoDB:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
Dies sind die relevanten SQL-Anweisungen:
create table if not exists menus (
id mediumint unsigned not null auto_increment,
id_restaurant mediumint unsigned not null,
name varchar(50) not null,
description varchar(255) default null,
foreign key(id_restaurant)
references restaurants(id)
on update cascade
on delete cascade,
primary key(id)
) engine=InnoDB;
create table if not exists items (
id mediumint unsigned not null auto_increment,
id_restaurant mediumint unsigned not null,
name varchar(50),
description text,
type enum('appetizer','salad','soup','entree','dessert','drink','other'),
price decimal(4,2),
foreign key(id_restaurant)
references restaurants(id)
on update cascade
on delete cascade,
primary key(id)
) engine=InnoDB;
create table if not exists order_items (
id_order bigint unsigned not null,
id_item mediumint unsigned not null,
item_name varchar(50),
item_description text,
item_price decimal(4,2),
notes varchar(1024),
quantity smallint unsigned,
foreign key(id_order)
references orders(id)
on update cascade
on delete cascade,
foreign key(id_item)
references items(id)
on update cascade
on delete cascade,
primary key(id_order, id_item)
) engine=InnoDB;
Durch das Entfernen von menu_items.id_menu und des entsprechenden Fremdschlüssels/Primärschlüssels können die SQL-Anweisungen richtig geparst werden.
Warum kann ich keinen Fremdschlüsselverweis auf menus(id) aus menu_items erstellen?