There is a potential problem with foreign keys.
You need indexes to support foreign keys if
a1) you plan to update or delete the corresponding
parent key values and
a2) you want to avoid a table-locking issue on the
child table.
or
b) the index that supports the foreign key happens to
be one you really wanted for efficiency reasons anyway.
So you may decide that some of your foreign key columns
do not need any index support. But if you have a primary key
that is supported by an index with extra columns added, then
an update on the non-key columns behaves like an update on
the primary key. So you could end up "KNOWING" that you
aren't updating the primary key, and still seeing child table locks.
(I think I've checked this in 8.1 and 9.2, but can't remember for
sure).
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
----- Original Message -----
From: "Sai Selvaganesan" <ssaisundar@(protected)>
To: <oracle-l@(protected)>
Sent: Tuesday, March 30, 2004 11:50 PM
Subject: pros and cons
: hi
:
: i read the follwing line in oracle 9i documentation,
:
: Unique and primary keys can use non-unique as well as unique indexes. They
can even use just the first few columns of non-unique indexes.
:
: i am planning to use a primary key constraint enforced by the first few
columns of a non-unique index.any feedback or advice or gotchas in having
such a setup.
:
: thanks
: sai
:
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
