Page 1 of 1
phpmyadmin foreign keys
Posted: Sat Jul 24, 2010 10:23 pm
by shawngoldw
I'm having some trouble implementing foreign keys in phpmyadmin.
I have 2 tables, both are set to innoDB.
Table A has a primary key on user_id
Table B has a primary key on post_id and an index on user_id.
In phpmyadmin I go into relational view on Table B but am having problems connecting it to Table A.
In the row for user_id I select Table A->user_id and cascade on delete and on update. Then I hit go but the form gets reset.
It turns out that no matter what I select the form resets unless the field I pick is the same field as the field I'm trying to set a reference for.
In this case the field only doesn't reset if in Table B, on user_id I pick Table B->user_id. This makes absolutely no sense.
Anyone have any idea what is going wrong here?
Re: phpmyadmin foreign keys
Posted: Sat Jul 24, 2010 11:49 pm
by JakeJ
Please provide a table export as well as the query you're trying to use. That will put the problem in much better focus for us.
Re: phpmyadmin foreign keys
Posted: Sun Jul 25, 2010 8:04 am
by shawngoldw
Code: Select all
CREATE TABLE `login` (
`login_id` int(10) unsigned NOT NULL auto_increment,
`user_id` int(10) unsigned NOT NULL default '0',
`token` char(32) NOT NULL default '',
`ip` char(15) NOT NULL default '',
`timestamp` datetime default NULL,
PRIMARY KEY (`login_id`),
KEY `user_id` (`user_id`),
KEY `timestamp` (`timestamp`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `user`
#
CREATE TABLE `user` (
`user_id` int(10) unsigned NOT NULL auto_increment,
`email` char(50) NOT NULL default '',
`password` char(128) NOT NULL default '',
`salt` char(128) NOT NULL default '',
PRIMARY KEY (`user_id`),
KEY `email` (`email`)
) TYPE=InnoDB;
I'm not running any particular query though, I'm using the interface in phpmyadmin and it is not showing me the query it uses.
What I am doing is this:
go to Table login
go to relation view
in the second column click the dropbox beside user_id
At this point I see login->login_id, login->user_id, login->timestamp, user->user_id
I select user->user_id
I select cascade on delete and on update
Click "go"
The page then refreshes and simply resets itself.
The only way I can get changes to stay is if I select login->user_id or login->login_id. But these are no help to me.
Re: phpmyadmin foreign keys
Posted: Sun Jul 25, 2010 2:39 pm
by JakeJ
Just for kicks. try creating two new tables for testing.
first:
id (primary key)
second:
id (primary key)
first_id (foreign key)
Get those two to play nice and then add a third table and another key.
Other than that, I'm not sure what's going wrong.
Re: phpmyadmin foreign keys
Posted: Sun Jul 25, 2010 8:54 pm
by shawngoldw
Those played nice right away. I don't know exactly what the problem is with the tables I'm trying to use but I have a feeling it has to do with the way they were created.
At first they were myisam and I later converted them to inno db for the foreign keys. Then there were some errors about the pma database not being set up properly and I had to create a new database with special pre-defined tables and edit a config file.
I'm just going to recreate my two tables tomorrow when I have some time and hopefully then they will work.
Thanks for the help
edit: I recreated them and they work