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@xxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------