Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: O'Reilly interview with Date

Re: O'Reilly interview with Date

From: Paul <paul_at_test.com>
Date: Thu, 11 Aug 2005 19:54:49 +0100
Message-ID: <42fb9efa$0$17490$ed2e19e4@ptn-nntp-reader04.plus.net>


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?

>
> personally i distinguish them with a prefix code, which is put onto the end
> of the column(s) names.

<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 - 13:54:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US