Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
protokol
Forum Contributor
Posts: 353 Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:
Post
by protokol » 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:
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?
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Mon Aug 16, 2004 12:52 am
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..
protokol
Forum Contributor
Posts: 353 Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:
Post
by protokol » Mon Aug 16, 2004 1:04 am
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 » Mon Aug 16, 2004 2:18 pm
FKs need to be indexed before they take effect. Try adding an index on the FK column in each table.