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