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

stimits at comcast.net stimits at comcast.net
Mon Sep 4 12:42:12 MDT 2017

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.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lug.boulder.co.us/pipermail/lug/attachments/20170904/13d34eb2/attachment.html>

More information about the LUG mailing list