Re: O'Reilly interview with Date

Kenneth Downs wrote:

>>>In my own system I never manually code a join, I just specify the two
>>>tables and it builds it out of the foreign key definitions.
>>
>>What if there is more than one foreign key constraint involving the two
>>tables?

<snip>

> which is equivalent to the following DDL:
>
> create table examples (
> SOMECOLUMN int PRIMARY KEY
> )
>
> create table examples_xref (
> SOMECOLUMN int REFERENCES examples(SOMECOLUMN),
> SOMECOLUMN_TO int REFERENCES examples(SOMECOLUMN)
> )

OK maybe I'm being dense here, but when you now specify your two tables, "examples" and "examples_xref", how does the system know which of the foreign key constraints to use to generate the join?

If it uses the one where the column names match, what if you meant to use the other one?

Presumably you may sometimes want one join and sometimes the other.

For example suppose I have a table of journeys, in the form of (departure,arrival) tuples, where these are codes for places.

Then I have a "lookup" table for the full names, etc. corresponding to these place codes.

I might sometimes want to join on departure and sometimes on arrival. And sometimes on both.

Paul. Received on Thu Aug 11 2005 - 20:54:49 CEST