This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes Meaning of Enable Novalidate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Meaning of Enable Novalidate" Watch "Meaning of Enable Novalidate" New topic

Meaning of Enable Novalidate

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 222

I created a table with a primary key constraint
Then I disabled the constraint and enabled it with enable novalidate.

And what I think enable novalidate do is "The constraint would be enabled without validating the constraint logic for the old existing data. Only the fresh new data would comply with the constraint logic."

Oracle Java Programmer , Oracle SQL Expert
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3606

This would be typically used in a situation where you have rows in the table that violate the constraint, but want to force any modifications to the table (inserts or updates) to conform to the constraint. It doesn't make much sense with primary key, though. But for foreign constraints, or check constraints this might be useful from time to time.

With a small modification this is often used in data warehouses and OLAP applications. If you have a very large table (say, billions of rows) and you know the data are conform with the constraint, you can set it to ENABLE NOVALIDATE RELY. The RELY is important here - it tells the database that all rows are conform with the constraint and the database can use this knowledge to answer queries (so if you use ENABLE NOVALIDATE RELY, but the constraint is actually violated by the data, your queries might give wrong results - no wonder, you've "lied" to the database).
It is sorta covered in the JavaRanch Style Guide.
subject: Meaning of Enable Novalidate
Similar Threads
To identify Primary Keys in a table
ORA-02291: integrity constraint violated - parent key not found
Obtaining Unique Constraints via dbMetaData
AST6: Mapping EJB / RDB With Views in Oracle9
Unable to Truncate Table / Drop Storage and Slow access. Why?