| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Foreign key constraints
On Fri, 18 Jun 1999 08:49:54 -0400, Kenneth C Stahl
<kstahl_at_lucent.com> wrote:
>Question: If a foreign key constraint is created for a column in a
>table, is it required that there be an index on that column? One book I
>read suggested that if there isn't an index for the column then there
>may be locking problems when updates occur.
Locking problems can occur if you delete or change records on the table being referenced. Say that I have an employee table with 100,000 records that all have foreign keys to the dept table. Then say that I delete one dept record. Oracle needs to check to see if any employees point to that record. If there is no index on the employee.dept field, Oracle will be forced to do a full tablescan, and it will lock the table to be sure that it remains stable while that occurs.
Jonathan
 |
 |