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

[stimits at comcast.net]

...

> Why would this surprise you?
Because it is the reverse of this example...edited below to illustrate.
 
...
    create table employee (
        employee_id int primary key,
        ....
    )
 
    create table another_company_employee (
       employee_id int primary key,
       ...
    )
 
    create table secret_identity_company (
       employee_id int primary key,
       ...
    )
 
    create table employee_diplomat (
        employee_id int references employee(employee_id) not null,
        employee_id int references another_company_employee(employee_id) not null,
        employee_id int references secret_identity_company(employee_id) not nulll,
        start_date timestamp not null,
        end_date timestamp,
        ...
   )
 
...so...there are not multiple columns referencing one column...there is a single column referencing multiple columns. The above is effectively an intersection of employees which are employed by three companies simultaneously, and is not a classification of an employee as both an employee and a manager. Three primary keys are treated as one key and becomes an intersection. I had not expected that...I only expected the original example and had assumed a column definition could occur only once (the extra references were added after the table was created with just one reference).
 
 
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/284a0664/attachment.html>