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.


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
>I got "ORA-01735: invalid ALTER TABLE option" on
>"using index tablespace idx_space".  How to use "using
>index space" with foreign key?
>Do you Yahoo!? 
>Yahoo! Mail - You care about security. So do we. 


Other related posts: