#205 relation : foreign->master

[Alexander Schuch]

I wanted pma to let the user click on 'MemberID'
(browse page) and show all entries from 'sites' table
which have the proper 'MemberID'. After figuring out how
pma stores that data I changed the proper entry
in 'relation' table and it works. Why isn't this possible
directly from pma? A bug or something else?

[Marc Delisle]

Logged In: YES
user_id=210714

I think I understand what you mean. Please send a dump of
the structure of your 2 tables.

[Alexander Schuch]

desired (shorten) dump of both tables

[Marc Delisle]

Logged In: YES
user_id=210714

What entry did you put in the 'relation' table?

When you talk about "non-primary key", are you referring to
your _Login field which is a UNIQUE key?

[Alexander Schuch]

Logged In: YES
user_id=501368

I put following entry into relation-table:

INSERT INTO relation VALUES
('database', 'members', '_ID', 'database', 'sites', '_MemberID');

Well, the script 'tbl_relation.php' just lists all primary keys of
all tables present in my database. When I talk of 'non-primary
keys' I mean all those keys which refer to a primary key,
i.e. 'sites._MemberID' refers to 'member._ID'
and 'members._RefererID' refers to 'members._ID'
(although 'members._ID' is not listed when I'm creating
relations of 'members' table).

But this relation should work the other way around too, i.e.
clicking on 'members._ID' should show all
proper 'sites._MemberID' which is not possible without
modifying the relation table directly.

[Alexander Schuch]

Logged In: YES
user_id=501368

When I want to create a relation to let's
say 'sites._MemberID' all other field names of all tables in my
database of the same type, i.e. unsigned int, should be listed
as there is no sense trying to match a int with a varchar.

[Anonymous]

Logged In: YES
user_id=509287

if i don't misunderstand you than i can't see any general
sense in that. it might be interesting for you in this one case,
but usually i have one master and many detail (=foreign)
tables. so if i browse a detail table i want to see which entry
in the master table it refers to, but if i browse the mastertable
which of the detailtables should i show? if i have only 3 of
them than still the chance that i show the right one will only
be 1/3.
i rather think it makes everything rather confusing.

[Alexander Schuch]

Logged In: YES
user_id=501368

In my opinion, all details should be shown, i.e. all 3 entries.
As in my case I'm interested in knowing how many sites a
member has registered.

Of course if the member can register let's say links too, it
won't be clear whether to show the sites related to that
member or the links related to him. Maybe one can
implement a possibility to choose what to show in case there
are more than one possible detail table.

[Marc Delisle]

13 years later, with the popularity of InnoDB, I switched the sample tables to InnoDB and with an index added to sites._MemberID, it's possible to create the foreign keys pointing in both directions. So I suggest to close this ticket, even if the intended behavior is not possible with internal relations.

[Isaac Bennetch]

I agree that because this is possible with InnoDB, we can make this wont-fix for internal relations. In an ideal world, we could implement this, but after 13 years it's better to close it now.