Forum Stats

  • 3,872,222 Users
  • 2,266,407 Discussions
  • 7,911,107 Comments

Discussions

SQL Assertions / Declarative multi-row constraints

1246729

Comments

  • pudge
    pudge Member Posts: 60

    This feature has been deferred for a couple decades due to the weighted truths of 1) machines not being fast enough yet and 2) more pressing needs for feature improvement trumped this feature, and never because it was a "bad" feature. In the burgeoning era of "big data" that often is only a sample that needs only to be statistically valid, completing this feature in the Oracle RDBMS part of the technology stack will further improve the possibility of developers efficiently building applications that deliver precisely true data that adhere the designed constrains. When precise truth rather than mere statistical representation of trends is the need, this seems clearly to be the very best next step in progress toward that goal. +42.

    user1077573
  • Iggy Fernandez
    Iggy Fernandez Member Posts: 29 Blue Ribbon

    How would you position the assertion functionality against the Oracle Rules Manager, which I think has been de-supported in release 12.x? Surely the two overlap, and I have wondered why Larry dropped it. I suspected that it was because of the propensity for consistency issues in complex environments. I am not voting against your proposal, only suggesting that people should think about it before clicking "up".

    For those not familiar with the Rules Manager, there is a nice example here, Oracle Rules Manager by example | Oracle FAQ

    Rules Manager cannot guarantee data integrity because (1) readers do not acquire read locks and hence do not block writers in Oracle Database; and (2)  table triggers in Oracle Database can run into the "mutating table" problem.

  • Iggy Fernandez
    Iggy Fernandez Member Posts: 29 Blue Ribbon

    This is going to be really difficult.

    Take the AT_MOST_ONE_PRESIDENT example. What if your statement inserts a set of rows, using INSERT INTO .... SELECT FROM .... and the set includes two PRESIDENTs? Depending on the the way the SELECT happens to execute, the PRESIDENTs might appear in a different order. Which one should throw the error? You can't have a statement give different results depending on how the CBO optimizes it. Ted Codd would spin in his grave.

    If it can be implemented in a way that can handle mutating tables and more complex situations, sure - but I can see this being a facility that is wide open to abuse and a dreadful capability for writing bug-ridden code.

    Be careful what you ask for.

    --

    John Watson

    Oracle Certified Master DBA

    re: dreadful capability for writing bug-ridden code.


    According to the proposal, the only code that application developers and database administrators would have to write write is CREATE ASSERTION statements.

  • NSK2KSN
    NSK2KSN Member Posts: 602 Bronze Badge

    This is one of the excellent features that should be there for sure

  • user11991904
    user11991904 Member Posts: 2 Red Ribbon

    +1 Great IDEA!

  • Dimitri Gielis
    Dimitri Gielis Member Posts: 1,962 Bronze Trophy

    +1 needed this feature multiple times before too, so I support this enhancement

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond

    The implementation would be interesting - and likely complex. And will add a new set of painful issues and errors. But then foreign key constraints are not that different.

    Have an upvote. +1

  • Toon_Koppelaars-Oracle
    Toon_Koppelaars-Oracle Member Posts: 100 Employee

    The implementation would be interesting - and likely complex. And will add a new set of painful issues and errors. But then foreign key constraints are not that different.

    Have an upvote. +1

    Hi Billy.

    What kind of issues and/or errors are you referring to wrt. foreign keys that you'd expect to come into play for SQL assertions too?

    Locking? Just interested.

    Thanks for your vote.


    Toon

  • Aman....
    Aman.... Member Posts: 22,919 Gold Crown