Forum Stats

  • 3,853,227 Users
  • 2,264,195 Discussions
  • 7,905,291 Comments

Discussions

SQL Assertions / Declarative multi-row constraints

1235728

Comments

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

    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

    Referring to the application side. Developer ignorance. Kind of like having a FK constraint that results in a FTS, with Oracle being blamed as the problem. Or expecting a sequence to generate gap-free numbers.

    The usual.

  • Sven W.
    Sven W. Member Posts: 10,551 Gold Crown
    edited May 23, 2016 8:46AM

    I can't make my mind up about it yet.

    There are possible SQL solutions in place already that can handle the use cases that were mentioned.

    - FKs (correct ER modelling techniques, for example to reflect 1 to 0 relationships)

    - function based unique keys (not mentioned yet). They can help to find solution for the use cases: AT_MOST_ONE_PRESIDENT, NO_TRAINERS_IN_BOSTON, AT_LEAST_ONE_CLERK_PER_DEPT

    - on commit materialized views

    - database triggers (needs plsql)

    What is a little underrated are function based unique keys. The AT_MOST_ONE_PRESIDENT use case could be implemented like this (not tested).

    create unique constraint AT_MOST_ONE_PRESIDENT_UK

    on emp (case when job='PRESIDENT' then 1 else null end);

    This is already an elegant way to solve certain types of issues.

    See also: https://docs.oracle.com/database/121/SQLRF/statements_5013.htm#BGEHDECJ

    If one needs to access data from a second table then a combination with a virtual column might be interesting. I'm not sure if that is allowed at the moment.

    The last use case "AllPartSupp" seems more like a business rule that is very likely to change in the future. Therefore as a developer I would implement it using some api to check certain types of business rules.

    I have the feeling that the proposed CREATE ASSERTION statement might impose several risks and drawbacks that are not easy to take care of.

    - Performance, especially serialization issues. ON COMMIT MVs already suffer from this performance problem.

    - implementation restrictions (similar to what mutation tables do a t the moment)

    - not editionable (db triggers are editionable and thereby easier to change if they resemble a business rule)

    Having said that, the VACATION_DURING_EMPLOYMENT is a good example where such an assertion might give a good declarative solution for a typical restriction.

  • padders
    padders Member Posts: 1,081 Silver Trophy

    Someone start a sticky titled 'Why can't I do an autonomous transaction inside my assertion'.

    jgarryApexBine
  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown

    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

    Billy~Verreynne wrote:
    
    The implementation would be interesting - and likely complex.
    

    What complexity?

    11g's Continuous Query Notification should already be capable of identifying when to run the CHECK based on the given SQL

    select * from dual where %assertion%

    Merging the aggregated result of one data set (eg what is in the table now) with the aggregated result of another data set (eg the new/old rows) is easily done by calling the aggregate's ODCIAggregateMerge() function.

    Of course, you'll need to store the "current aggregated result" somewhere.

    And managing "current aggregate result" in a table is best done by creating your own INDEXTYPE. (I don't think this method has been discussed.)

    I have a feeling that 12c's Row Pattern Matching has a major part to play in the implementation as well.

    SQLs in that format should make it easy to extract out the "partition by/order by" clause along with which aggregate(s) (aka measures) are used.

    thus allowing row level locking for only the affected rows.

    ("complexity" might be at this stage.. but the CBO can already rewrite your queries. Can it not? )

    @Sven W. - I see Serialization happening at the "partition by/order by" level.  Locking would probably resemble the method used for proper implementation of a "gap free sequential numbers" requirement. (ie row-lock a "parent table" that uses the "partition by/order by" columns as the PK).


    Since all the major components needed to implement this already exist, I'm sure Oracle already has an alpha release somewhere that works with simplistic assertions.

    IMHO - This post feels more like a teaser.

    My $0.02

    MK

  • Sven W.
    Sven W. Member Posts: 10,551 Gold Crown
    SQL assertions can be used to implement what’s commonly called cross-row constraints, or multi-table check constraints. In short a SQL assertion is a CHECK constraint at the database level that is allowed to contain queries.
    

    I'm in favor of cross-row constraints.

    I'm slightly against multi-table check constraints.


    A multi table check constraint imho is an indication for a poor data model or a rule that might be better implemented in a different layer of the application.

    There are some noticable exceptions for this. Especially validation rules that span parent-child relationships (like in the VACATION_DURING_EMPLOYMENT example).


    APC
  • Toon_Koppelaars-Oracle
    Toon_Koppelaars-Oracle Member Posts: 100 Employee
    SQL assertions can be used to implement what’s commonly called cross-row constraints, or multi-table check constraints. In short a SQL assertion is a CHECK constraint at the database level that is allowed to contain queries.
    

    I'm in favor of cross-row constraints.

    I'm slightly against multi-table check constraints.


    A multi table check constraint imho is an indication for a poor data model or a rule that might be better implemented in a different layer of the application.

    There are some noticable exceptions for this. Especially validation rules that span parent-child relationships (like in the VACATION_DURING_EMPLOYMENT example).


    Sven W. wrote:
    A multi table check constraint imho is an indication for a poor data model ...
    

    Not sure how to respond to this...

    But then with all the "noticeable exceptions" you add thereafter, the majority of multi-table check constraints luckily aren't anymore such indication ;-)

    FatMartinR
  • jgarry
    jgarry Member Posts: 13,844 Gold Crown

    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

    Ah, so that's why Mark and Safra got kicked upstairs.  No assertion for CEO.

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond

    Doesn't seem easy.

    (what is the term people use ?  "it will be 'non-trivial' ")

    First Oracle has to test to see if the ASSERTION rule has to be applied.  Then, it has to apply the ASSERTION rule if the test returns TRUE.  And there's the complexity of concurrent INSERTs / UPDATEs.

    Hemant K Chitale

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

    Doesn't seem easy.

    (what is the term people use ?  "it will be 'non-trivial' ")

    First Oracle has to test to see if the ASSERTION rule has to be applied.  Then, it has to apply the ASSERTION rule if the test returns TRUE.  And there's the complexity of concurrent INSERTs / UPDATEs.

    Hemant K Chitale

    Hemant K Chitale wrote:
    
    First Oracle has to test to see if the ASSERTION rule has to be applied.  
    

    Check, we have this one covered.

    Then, it has to apply the ASSERTION rule if the test returns TRUE. 
    

    Check, covered too, also it'll do a delta check (i.e. not re-evaluate the whole assertion expression) whenever possible.

    And there's the complexity of concurrent INSERTs / UPDATEs.
    

    Check, also covered. There will be serialization between concurrent transactions. This is inevitable for the implementation to be correct (just like with FK's). The serialization will be minimal though, and only happen when necessary.

    ApexBine
  • Toon_Koppelaars-Oracle
    Toon_Koppelaars-Oracle Member Posts: 100 Employee
    Billy~Verreynne wrote:
    
    The implementation would be interesting - and likely complex.
    

    What complexity?

    11g's Continuous Query Notification should already be capable of identifying when to run the CHECK based on the given SQL

    select * from dual where %assertion%

    Merging the aggregated result of one data set (eg what is in the table now) with the aggregated result of another data set (eg the new/old rows) is easily done by calling the aggregate's ODCIAggregateMerge() function.

    Of course, you'll need to store the "current aggregated result" somewhere.

    And managing "current aggregate result" in a table is best done by creating your own INDEXTYPE. (I don't think this method has been discussed.)

    I have a feeling that 12c's Row Pattern Matching has a major part to play in the implementation as well.

    SQLs in that format should make it easy to extract out the "partition by/order by" clause along with which aggregate(s) (aka measures) are used.

    thus allowing row level locking for only the affected rows.

    ("complexity" might be at this stage.. but the CBO can already rewrite your queries. Can it not? )

    @Sven W. - I see Serialization happening at the "partition by/order by" level.  Locking would probably resemble the method used for proper implementation of a "gap free sequential numbers" requirement. (ie row-lock a "parent table" that uses the "partition by/order by" columns as the PK).


    Since all the major components needed to implement this already exist, I'm sure Oracle already has an alpha release somewhere that works with simplistic assertions.

    IMHO - This post feels more like a teaser.

    My $0.02

    MK

    Definitely not a teaser...