| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: Primary keys vs. unique indexes
Well, if you don't reference back to a unique row in the other table, then
you essentially have a many-to-many relationship (instead of a one-to-<zero,
one, or many>). Without the uniqueness, you would be saying that all of
these rows in this table relate to all of these rows in this table (and if
you try to join them together, you will be looking at a cartesian join
unless you only have one row on either side of this strange relationship).
With this in mind, most database systems implement foreign keys to reference
a unique constraint (primary key or unique) so that they are guaranteed to
link back to one (and only one) row in the parent table. Now to get some
really interesting thoughts going...say you have 50 rows in a child table
that reference 20 rows in the parent table (from lack of uniqueness), and
imagine that this table is a child table to another table and each one of
those 20 rows has a different "key" back to this other parent table (with
each relationship being between say 1-10 rows each). Trying to track down
how your data fits together (or even trying to resolve logical data
corruption problems) in this scenario could get pretty confusing (and this
is just 3 tables related to each other). I hope this helps...if not, feel
free to email me (privately or to the list) with any other questions you
might have.
Jeffery Stevenson
Chief Databeast Tamer
Medical Present Value, Inc.
Austin, TX
-----Original Message-----
From: Cyril Thankappan [mailto:cyril_thank_at_rediffmail.com]
Sent: Saturday, December 02, 2000 1:20 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: RE: Primary keys vs. unique indexes
Hi!
Yes true, but I have not been able to understand WHY the primary key is required to define the foreign key..
Please understand me, I am aware that Oracle has put a restriction of primary/unique key being pre-requisite for foreign key definition, but I am trying to understand the 'reason' behind imposing that restriciton..
Perhaps some design 'guru' can help...
Hannah r u listening!
of course you can.
to quote the 8.1.6 SQL Reference manual restrictions on foreign keys:
The referenced UNIQUE or PRIMARY KEY constraint on the parent table must already be defined.
seems to me that that is pretty clear
>From: "Koivu, Lisa" <lkoivu_at_qode.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Primary keys vs. unique indexes
>Date: Thu, 30 Nov 2000 09:20:22 -0800
>
>What? Yes you can. I have anyway.
>
>Can you give me an example of when that would not work?
>
>Lisa Rutland Koivu
>Oracle Database Administrator
>Qode.com
>4850 North State Road 7
>Suite G104
>Fort Lauderdale, FL 33319
>
>V: 954.484.3191, x174
>F: 954.484.2933
>C: 954.658.5849
>http://www.qode.com
>
>"The information contained herein does not express the opinion or position
>of Qode.com and cannot be attributed to or made binding upon Qode.com."
>
>
>-----Original Message-----
>Sent: Thursday, November 30, 2000 11:51 AM
>To: Multiple recipients of list ORACLE-L
>
>
>The main difference is you can not reference an unique
>index with a foreign key.
>--- cemail_at_sprintmail.com wrote:
> >
> > What is the difference between building a table and
> > including
> > the primary key versus building the table and then
> > having creating
> > a unique index on the same keys? When you create a
> > table with
> > a primary key, doesn't Oracle store it as an index
> > anyway?
> >
> > -----
> > Sent using MailStart.com (
> > http://MailStart.Com/welcome.html )
> > The FREE way to access your mailbox via any web
> > browser, anywhere!
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author:
> > INET: cemail_at_sprintmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>--------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
>__________________________________________________
>Do You Yahoo!?
>Yahoo! Shopping - Thousands of Stores. Millions of Products.
>http://shopping.yahoo.com/
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: james ellis
> INET: jellis24_gso_at_yahoo.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: carmichr_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _____________________________________________________ Chat with your friends as soon as they come online. Get Rediff Bol at http://bol.rediff.com Participate in crazy auctions at http://auctions.rediff.com/auctions/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cyril Thankappan INET: cyril_thank_at_rediffmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Mon Dec 04 2000 - 08:23:19 CST
 |
 |