RE: Table Locks Adding Foreign Keys

Todd,

Think of it as a long-running query on the parent table. It should not throw any locks.

I also agree with Mladen. Create the Fk with the 'enable novalidate' option. It will create the FK immediately with no further processing. The only downside of this is that existing records will not have this column validated. If the column is empty, then you are fine. If it is not empty, you can run your own queries against the table to see if all of the values are valid. I think I would do this to ensure that the values are valid anyway before I applied the FK constraint.

Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

From: todd.sheetz_at_aurora.org [mailto:todd.sheetz_at_aurora.org] Sent: Wednesday, October 06, 2004 12:49 PM To: oracle-l_at_freelists.org
Subject: Table Locks Adding Foreign Keys

This probably has a very easy answer, but I can't seem to find it. I have a 260 million row table that I need to add a foreign key constraint to that references another table with 30 million rows. Will adding the constraint cause any locking during the time the constraint is being validated? This is a vendor application and they are telling us that we can run this while users are in the system. I have some doubt about whether this would place at least a share lock on the table. Any information is appreciated.

Thanks

Todd

--

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

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 06 2004 - 12:10:17 CDT