|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
InnoDB foreign key problem
hey guys.. wonder if anyone might be able to help with my current situation.. ive got 2 tables :
DROP TABLE IF EXISTS users; CREATE TABLE users ( usr_id int unsigned NOT NULL auto_increment, usr_name varchar(30) NOT NULL unique key, usr_pass varchar(32) NOT NULL, etc.. usr_level int unsigned NOT NULL unique key default '1', PRIMARY KEY (usr_id), FOREIGN KEY (usr_level) REFERENCES user_levels (l_level) ) TYPE=InnoDB; and DROP TABLE IF EXISTS user_levels; CREATE TABLE user_levels ( l_level int unsigned NOT NULL unique key default '1', l_level_desc varchar(100) NOT NULL default '', PRIMARY KEY (l_level) ) TYPE=InnoDB; recently i was getting errno150 (cant write table) until i realised i had to add "unique key" to "usr_level" in users table so that it matched the "unique key" attribute that "l_level" has in user levels table.. (InnoDB requires this) now it adds fine.. but the problem after adding the unique is that i cant have many users with same access level (which i wanted).. can anyone recommend a way under innodb to keep the l_level in user_levels unique.. but not its foreign key in users so that it can be a 1 to many relationship? thanks for your help!! ![]() |
|
#2
|
|||
|
|||
|
seem to of sorted it.. but adding KEY(usr_level) or INDEX(usr_level) to users table..
|
![]() |
| Viewing: Dev Articles Community Forums > Databases > MySQL Development > InnoDB foreign key problem |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|