to me the only right answer (really) is a) index them or b) use a surrogate key -- you obviously don't have a primary key here! "not validated can affect the optimizer unless you set query rewrite integrity" How exactly does 'not validated' affect the optimizer? then the UK is not a UK -- look you are updating a thing that by your definition "dozens of columns point to". Yes, dozens of columns point to that UK, but I am talking about adding a new value to the UK by mistake or business changes their mind.Since this is a new value, apps havent started to use it yet, so even though the FK exists, in reality, no data really points to this new row.In an ALTER TABLE statement, ENABLE NOVALIDATE resumes constraint checking on disabled constraints without first validating all data in the table. The constraint is not checked and is not necessarily true.
[email protected] alter table t enable novalidate constraint uniq_a; Table altered.Or, you can create the constraint as normal, lose the index (which may be equally as desirable as keeping it depending on your circumstances of course) and create a NON-Unique index manually: [email protected] alter table t enable novalidate constraint uniq_a; alter table t enable novalidate constraint uniq_a * ERROR at line 1: ORA-02299: cannot validate (OPS$TKYTE.No new data has been added that uses the new value I just added.So I go ahead and 'enable novalidate' which is very fast.No, enable novalidate isn't safe to me, not unless you shutdown the database and do this in single user mode. "b) use a surrogate key -- you obviously don't have a primary key here! IMO you have two reasonable, realistic, safe, correct choices.
not validated can affect the optimizer unless you set query rewrite integrity. I would not go down the path you are, it not in that list preceding. "then the UK is not a UK -- look you are updating a thing that by your definition "dozens of columns point to". period." I agree with everything you are saying but you are missing one small nuance.
If I disable RI constraint, load orphaned data into child table and try to enable the RI, I always get an error saying that parent keys not found.
If Oracle is not trying to validate on enable, then why I get this error?
What if I UPDATE a existing child record such that it doesnt obey the constraint? I guess what I am really asking is it "safe" to use ENABLE NOVALIDATE and what are the ramifications of doing so?
Thanks 3) how long does it take to update this row? if the fkeys are unindexed -- it is going to take a boatload of time as ALL OF THE TABLES that have the unindexed fkey must be full scanned. seems to me, if this is a common recurring problem -- you need to use a surrogate key on this table, so you NEVER change the "primary key" Yes, most of the FK columns pointing to this table are not indexed and that is the main reason it takes so long. And all the dozens of FKs point to one of these 2 UKs.
Can you please demonstrate what DISABLE VALIDATE means exactly? dozens of tables have a FK pointing to this small lookup table. To prevent the massive row-level locking that would happen if I just go ahead and update the lookup table, I disable and re-enable all the FK constraints.