stupid foreign key problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

stupid foreign key problem

Post by protokol »

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:

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 ;
I get this error:

Code: Select all

#1005 - Can't create table '.\sleek\access_level_in_group.frm' (errno: 150)
What gives?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

looks like your php doesn't have write access to the folder..

you can do references without using innodb.. but you have to manually link them through joins.. of standard tables..
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Post by protokol »

OK, this is not a PHP problem. I'm doing this in the MySQL console. Also, I need the InnoDB foreign key restraint feature so that my SQL is cleaner and my PHP code doesn't have to do a bunch of work. However, I solved the problem. The correct SQL is below:

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,
  INDEX (acc_id),
  FOREIGN KEY (acc_id) REFERENCES access_level (acc_id) ON DELETE CASCADE,
  INDEX (group_id),
  FOREIGN KEY (group_id) REFERENCES access_group (group_id) ON DELETE CASCADE
) TYPE=InnoDB;
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

FKs need to be indexed before they take effect. Try adding an index on the FK column in each table.
Post Reply