phpmyadmin foreign keys

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
shawngoldw
Forum Contributor
Posts: 212
Joined: Mon Apr 05, 2010 3:38 pm

phpmyadmin foreign keys

Post 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?
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: phpmyadmin foreign keys

Post 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.
shawngoldw
Forum Contributor
Posts: 212
Joined: Mon Apr 05, 2010 3:38 pm

Re: phpmyadmin foreign keys

Post 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.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: phpmyadmin foreign keys

Post 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.
shawngoldw
Forum Contributor
Posts: 212
Joined: Mon Apr 05, 2010 3:38 pm

Re: phpmyadmin foreign keys

Post 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
Post Reply