Re: [Q] create foreign key problem???
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: mccdba1@xxxxxxxxx
- Date: Thu, 20 Jan 2005 23:12:22 +0100
Simple. You don't.
Constraints and indexes are unrelated things. Unique and primary key
constraints happen to be implemented through unique indexes, but it's
nothing but an implementation trick performed by Oracle behind your
back. Foreign keys don't need an index, per se.
What happens, however, is that when referenced table and referencing
table are concurrently modified, if the foreign key is not indexed then
Oracle acquires (or tries to acquire) exclusive locks on both objects,
which usually generates dead locks (the reason for that is, as far as I
understand it, that since when you want to delete a row from the
referenced table you must check for a child row, it can take *some time*
if the foreign key is not indexed. Unfortunately, this is a case when
read-consistency can play against us, since in the mean-time somebody
may have inserted a row referencing the one we want to delete. Oracle
plays safe, and locks everything - once again, my understanding). This
is the reason why it is a common practice to index foreign keys, but it
has nothing to do with referential integrity, and in fact it is utterly
necessary if your reference table is a 'true' reference table (as
opposed to the master table in a master/detail relationship) and is very
rarely updated.
HTH
S Faroult
dba1 mcc wrote:
>I tried to create foreign ley constraint with index
>using following command:
>
>
>alter table spinfo add constraint fk11 foreign key
>(cbnum) references primary_info using index tablespace
>idx_space
>
>I got "ORA-01735: invalid ALTER TABLE option" on
>"using index tablespace idx_space". How to use "using
>index space" with foreign key?
>
>Thanks.
>
>
>
>
>__________________________________
>Do you Yahoo!?
>Yahoo! Mail - You care about security. So do we.
>http://promotions.yahoo.com/new_mail
>--
>//www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
