• 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

Why to set constraint Defferrable

 
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

Why to use Defferrable when we can disable the constraint and later enable it.
So what other functionality Defferable gives.
 
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
It's not the same. Disabling/enabling a constraint is a DDL operation, it means it will commit your current transaction. Therefore, you cannot disable and then re-enable a constraint all as a part of a single transaction.

Deferrable constraint, on the other hand, can be "switched off" inside a transaction and be evaluated only when the transaction commits, or when set back to immediate within the transaction. Database is therefore never seen by other sessions to be in a state that is not conform with the constraint*. This wouldn't be true in the first case.

* At least some versions of Oracle have bugs due to which a constraint that was deferred in a transaction can end up being violated after commit, when the data protected by the constraint were modified by different sessions concurrently. I've seen it myself. This decreases the utility of deferrable constraints greatly, of course.

Have you read the Database Concepts Guide, as I have suggested elsewhere? It might look like a lot of read, but it will save you the time in the long run. It's really a great introduction into Oracle (or databases in general) even for people with little prior knowledge - at least according to my own experience.
 
Popeye has his spinach. I have this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic