Re: [Q] create foreign key problem???

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
>--
>http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l