[lug] Q. on SQL Foreign Keys (PostgreSQL)

[stimits at comcast.net]

...
> > Nowhere can I find a table relating the foreign column to another table's column unless everything was >single column to start with.
>
>In postgresql anyway, in a foreign key constraint for a table, one column has a foreign>key relationship with exactly one other column even if the constraint has multiple>columns. Note that a table can have multiple foreign key constraints.
>
>So you should only have a one-to-one mapping between columns per foreign-key constraint.
>
>From postgresql docs 9.5.7 section 5.3.5:
>
>A foreign key can also constrain and reference a group of columns. As usual, it then needs>to be written in table constraint form. Here is a contrived syntax example:
>
>CREATE TABLE t1 (> a integer PRIMARY KEY,> b integer,> c integer,> FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)>);
 
This is exactly the kind of declaration which I'm having trouble extracting via ANSI information_schema. Although "b" should have a query showing it relates to "c1", and "c" should have a relation which can be found through query relating it to "c2", I find my queries can't understand the 1:1 correspondence. The answer should be something like:
b->c1
c->c2
...instead I get:
b->c1
b->c2
c->c1
c->c2
 
I keep thinking someone who has a better understanding either ANSI information_schema or the PostgreSQL non-portable tables might be able to shed light on doing this via query (ANSI is better since it might work on other databases, but I'm willing to satisfy with a non-portable SQL query). When a single column is declared in a single foreign key statement my queries work correctly. When multiple columns are declared in a single statement my queries cannot distinguish between first_column->first_destination and second_column->second_destination. Thus, my JOIN is wrong. I have been searching for a very long time as to how to change the JOIN to fix the missing 1:1 relation.
 
>
>Of course, the number and type of the constrained columns need to match the number and>type of the referenced columns._______________________________________________Web Page: http://lug.boulder.co.usMailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lugJoin us on IRC: irc.hackingsociety.org port=6667 channel=#hackingsociety
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lug.boulder.co.us/pipermail/lug/attachments/20170829/daaaa9ec/attachment.html>