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

[stimits at comcast.net]

...
> On 08/23/2017 04:27 PM, stimits at comcast.net wrote:>> My concept is that even if multiple columns are being required in combination to be unique that the data in >a particular column is itself determined only by the knowledge of another single column.
>
>Not following this. The compound key usage I know has> T1.A -> T2.A> T1.B -> T2.B
 
This is how I believe it works, but I'm trying to verify. My current query is probably wrong because it results in something like:
T1.A->T2.A
T1.A->T2.B
T1.B->T2.A
T1.B->T2.B
(...note T1.A pointing at multiple providers...my JOIN is wrong but I'm struggling to understand ANSI information_schema in a way which allows one-and-only-one column-to-column relation...there does not seem to be a way to ignore T1.A->T2.B and T1.B->T2.A using information_schema...I was hoping someone here knows more about information_schema since it is ANSI)
 
So this does probably confirm the query JOIN is wrong instead of my concept of one-to-one column-to-single-column foreign key relation.
  >where you join on A & B in both tables. The fields A and B need not have a>uniqueness constraint in either table. So what are the foreign key dependencies>here? T1.A depends on both T2.A and T2.B?
>
 
So far as I know multiple columns of the table with the foreign key are combined in a single constraint such that T1.A and T1.B are unique, and such that T1.A points at a different table.column, and T1.B also points to a different table.column. I'm struggling to find a way to limit the result to a single column pointing at the single column...the query fails because it can't distinguish which of the foreign columns corresponds to one particular local column.
 
>Or are you referring to a situation where T1.X is a string concatenation>or a struct with values from T2.A, T2.B, T2.C? Saying that T1.X has>a foreign key depending on T2.A, T2.B & T2.C would make sense here.
 
I was thinking perhaps my idea of a single column pointing at a single column might fail if it were possible for a foreign key to be defined as a concatenation of two text/varchar columns. I don't think these tables do this, but if it is possible to do so, then I probably have to program to take this into account. Every time I simplify and don't include corner cases I find I have to start over.
 
>If you can find joins with the fields in question maybe it will clarify things.>Knowing if these relationships are supposed to drive cascading operations (e.g.>delete) would probably also help.
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lug.boulder.co.us/pipermail/lug/attachments/20170825/6fa7c17a/attachment.html>