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

[Bear Giles]

You've spent so much time trying to do this with information_schema you
would probably come out ahead with a one-shot program that provides the
necessary abstraction.

On Tue, Aug 29, 2017 at 11:58 AM, <stimits at comcast.net> wrote:

> ...
> > > 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.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
> Join us on IRC: irc.hackingsociety.org port=6667 channel=#hackingsociety
>
> _______________________________________________
> Web Page:  http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
> Join 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/86466594/attachment.html>