• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Meaning of Enable Novalidate

 
Ranch Hand
Posts: 391
1
MySQL Database PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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."

 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).
 
reply
    Bookmark Topic Watch Topic
  • New Topic