stupid foreign key problem
Posted: Mon Aug 16, 2004 12:41 am
OK, I'm having one hell of a time getting this to work. Basically I have 3 tables. The first table defines access levels, the second defines access groups, the third defines which access_level is in which group. This third table needs to have foreign keys to reference access_group.group_id and access_level.acc_id but for some reason, the following doesn't work:
I get this error:
What gives?
Code: Select all
CREATE TABLE access_group (
group_id int(11) NOT NULL auto_increment,
description varchar(25) NOT NULL default '',
PRIMARY KEY (group_id),
UNIQUE KEY description (description)
) TYPE=InnoDB;
CREATE TABLE access_level (
acc_id int(11) NOT NULL auto_increment,
level varchar(255) NOT NULL default '',
description varchar(255) NOT NULL default '',
PRIMARY KEY (acc_id),
UNIQUE KEY level (level)
) TYPE=InnoDB;
CREATE TABLE access_level_in_group (
acc_id int(11) not null,
group_id int(11) not null,
KEY acc_id (acc_id,group_id),
FOREIGN KEY (acc_id) REFERENCES access_level (acc_id) ON DELETE CASCADE,
FOREIGN KEY (group_id) REFERENCES access_group (group_id) ON DELETE CASCADE
) TYPE=InnoDB ;Code: Select all
#1005 - Can't create table '.\sleek\access_level_in_group.frm' (errno: 150)