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

[Bear Giles]

Why would this surprise you?

A classroom is example is

  create table employee (
      employee_id int primary key,
      boss int references employee(employee_id),
      ....
  )

but i practice you'll probably have something closer to

    create table employee (
        employee_id int primary key,
        ....
    )

    -- track an employee's managers over time
    create table employee_mgr (
        employee_id int references employee(employee_id) not null,
        manager_id int references employee(employee_id) not null,
        start_date timestamp not null,
        end_date timestamp,
        ...
   )

and there are countless other examples. Sometimes it's a result of a poor
design but sometimes it's just an intrinsic part of what you're modeling.


On Mon, Sep 4, 2017 at 12:42 PM, <stimits at comcast.net> wrote:

> I finally made a discovery which might be entertaining for anyone
> following this. It seems the query and the code I'd written were actually
> correct (I had set an assert to warn against multiple references of a
> foreign key since I did not think this would be valid...the assert itself
> was not valid).
>
> The error was that I thought a single column of a foreign key could not
> have simultaneous dependencies on multiple columns of multiple tables (not
> to be confused with a multi-column key...this is a single column pointed at
> multiple foreign columns...a single column had been marked as a foreign key
> several times).
>
> I had decided to give up parsing the SQL structure through the ANSI
> tables, and instead to read the pg_dump which was suggested earlier since
> it makes explicit reference to an exact column for a foreign key to point
> at. It turns out there were multiple statements on this huge database dump
> where a single column was given reference to columns of another table. This
> is essentially like a case of multiple inheritance resulting in an
> intersection join of other table columns. There really were several cases
> of one column pointing to a foreign column multiple times, and it wasn't a
> bug. I did not know that could happen. I've changed my data model, but the
> original query to find columns which point to foreign columns is valid :)
>
> ----- Original Message -----
> From: Bear Giles <bgiles at coyotesong.com>
> To: Boulder (Colorado) Linux Users Group -- General Mailing List <
> lug at lug.boulder.co.us>
> Sent: Wed, 30 Aug 2017 00:53:43 -0000 (UTC)
> Subject: Re: [lug] Q. on SQL Foreign Keys (PostgreSQL)
> 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.
> ...
>
> _______________________________________________
> 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/20170904/6fb8b50f/attachment.html>