Re: Composite foreign key question

In general I agree with you, but unfortunately it is also very easy to answer the question without reading it first. Note particularly:

> that reference individual primary keys in two separate tables?

Your diagram simply answers the question - how do I create a foreign key constraint.

The original poster seems to have

    t1(c1 primary key)
    t2(c2 primary key)
    t3(c1, c2 primary key)

and wants to know if (c1,c2) can be a 'foreign key' in some way to both t1 and t2. In other words (I think) can you enforce the fact that an example of (c1, c2) may only exist in t3 if (c1) exists in t1 ands (c2) exists in t2.

If this is the required target, then you just need two single column foreign key constraints from t3 to t1 and t2. The index generated for the primary key on t3 will satisfy any need for an index on one of the foreign keys, and the other foreign key can be indexed separately if required.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Mike Krolewski wrote in message <91os3u$pq4$1_at_nnrp1.deja.com>...


>In article <977264993.714036_at_news.zipcon.net>,

>  "joebob" <joebob_at_zipcon.n0t> wrote:

>> I've got a primary key made up of two columns.  Is it possible to

 create =


>> a composite foreign key made up of the two pk columns and

 that
 reference =


>> individual primary keys in two separate tables?  If so, please could

 you =


>> provide a little syntax ..

>>

>> Thank you

>>

>> --=20

>> To reply directly, replace the zero in my email address with the

 letter =


>> e.

>>

>>

>

>Reading from the manual,

>

>alter table <table> modify constraint <constraint> foreign key (

><column1>, <column2> ) references <foreignTable> ( <foreignColumn> ,

><foreignColumn2> )

>

>It is important if you really want to work on and with Oracle to spend

>some time reading the manuals and buy and read the auxillary texts. It

>is apparent to most of the answerers of these questions that most of

>the writers are not even attempting to read the manual.

>

>This syntax is obviously not something most people would recall off the

>top of their head. However, it takes about 2 minutes to bring up the

>manual in PDF, lookup 'alter table' and follow the boxcar diagram to

>the logical conclusion.

>

>--

>Michael Krolewski

>Rosetta Inpharmatics

>mkrolewski_at_rii.com

>              Usual disclaimers

>

>

>Sent via Deja.com

>http://www.deja.com/