apps-infra

    Forum Stats

  • 3,874,066 Users
  • 2,266,672 Discussions
  • 7,911,718 Comments

Discussions

SQL Assertions / Declarative multi-row constraints

12324252628

Comments

  • ErwinSmout
    ErwinSmout Member Posts: 15 Green Ribbon

    That was an old post of mine.  As time goes one, the wording may be different, but my stance remains.

    Tying in a more recent post (5 back) to the one you quoted:

    DATA ASSERTION === static assertion === currently available constraints === possibly only 1 of your 6 categories (maybe 4)

    DATA ASSERTIONs are always valid irregardless any context.

    APPLICATION ASSERTION === dynamic constraints === just about everything else.

    APPLICATION ASSERTIONs require a context (eg SYS_CONTEXT/UPDATING/INSERTING/DELETING)

    The APPLICATION ASSERTIONS that should be implemented along with DATA ASSERTIONS should be the ones that are based on a DATA ASSERTION.

    Example:

    create table students (  student_id int generated by default on null as identity,  constraint student_pk primary key (student_id));create table classes (  class_id int generated by default on null as identity,  max_students int,  class_name   varchar2(40 char),  constraint classes_pk primary key (class_id));create table enrollments (  student_id  int references students,  class_id    int references classes,  constraint enrollments_pk primary key (class_id, student_id));

    Initial "Business Requirement" - a class should not contain more than defined

    At top level, this appears to be a DATA ASSERTION that can be defined with:

    NOT EXISTS (  select null  from classes c    join students s on c.class_id=s.class_id  group by c.class_id, c.max_students  having count(*) not between 0 and c.max_students)

    But, real life doesn't work like that

    The additional "Business Requirements" include are:

    • People in Student Administration are allowed to override the "students per classroom" limit (but not change max value)
    • De-enrollment (DELETE) should still succeed even if class is still overbooked.

    That is - These are APPLICATION ASSERTIONS that can be defined as:

    NOT EXISTS (  select null  from classes c    join students s on c.class_id=s.class_id  group by c.class_id, c.max_students  having count(*) not between 0 and    CASE      when DELETING then MAX_NUMBER_VALUE      when SYS_CONTEXT('my app','is_student_administration') = 'YES' then MAX_NUMBER_VALUE      else c.max_students    END)

    This would be based on the DATA ASSERTION of:

    NOT EXISTS (  select null  from classes c    join students s on c.class_id=s.class_id  group by c.class_id, c.max_students  having count(*) not between 0 and MAX_NUMBER_VALUES

    Now, the DATA ASSERTION will always be true.  However, that ASSERTION is needed to provide the mandatory locking/access control to validate the APPLICATION ASSERTION.

    As far as implementing these types of APPLICATION ASSERTION in addition to DATA ASSERTION, it shouldn't be too complicated.

    Example:  if this was implemented using the Materialized View technique (I'm just using it as an example)

    The MV Table would look like this:

    create table for_assertions (  class_id              int,  max_students          int,  current_student_count int,  constraint for_assertions_pk primary key (class_id),  constraint DATA_ASSERTION check (current_student_count between 0 and MAX_NUMBER_VALUE));create or replacetrigger APPLICATION_ASSERTIONbefore insert or update or deleteon for_assertionsfor each rowasbegin  :new.current_student_count between 0 and    CASE      when DELETING then MAX_NUMBER_VALUE      when SYS_CONTEXT('my app','is_student_administration') = 'YES' then MAX_NUMBER_VALUE      else :new.max_students    END;end;

    Because they are so similar, they should be implemented together.

    If you have any more questions about my (in)sane concept, let me know

    MK

    All of that may or may not be "reasonable" from a business perspective, but here are the facts :

    ASSERTIONs as per the SQL standard are intended to cover only static constraints (my category 4) to which *there are no exceptions*

    ASSERTIONs whose check expression includes contextual elements are crippled by definition, and ought to be rejected (as Toon intends).

    The kind of "dynamic constraint" (as per Toon's definition in this thread) is out of scope for ASSERTIONs as per the SQL standard (and of all forms of declarative SQL altogether).

    If your business insists on having a rule and the very second it's declared starts thinking about when to exempt from it, then what this business is actually asking for is a "category 6" ("dynamic") contraint (or sets of them), and there no longer is any case for any "category 4" ("static") constraint.

    That's doable too, and has already been done, but it's an extension to SQL, whereas just supporting CREATE ASSSERTION is not.

    Brief : CREATE ASSERTION means basically "no exceptions for nobody, never, nowhere" and you seem to have a more pessimistic perception of what percentage of actual business cases are actually in that ballgame.

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

    We are interested to get to know some names of customers who would adopt SQL assertions when we deliver them.

    No need to post here: email me at [email protected].

    Thanks.

    Kim Berg HansenberxPDVBV
  • ahgonzalez
    ahgonzalez Member Posts: 1 Red Ribbon

    great idea, please make it happen!

    Toon_Koppelaars-Oracle
  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge
  • Beilstwh
    Beilstwh Member Posts: 721 Bronze Badge

    Wonderful feature. I vote 100% to implement this

  • PDVBV
    PDVBV Member Posts: 1 Blue Ribbon

    +1.

    I just got a mail about status change for this idea..

    I remember Voting Yes, waaay back already though.

    how else can I help ?

    I'll mail you my current customers and will try to advocate the idea..

  • Mike Kutz
    Mike Kutz Member Posts: 6,253 Gold Crown

    Voting is open again:

    • yes - we need to be able to define more advanced assertions than what we have now.
    • no - I don't think we should be using a 50yo syntax, that nobody (major RDBMS) has implemented, that requires you to think in terms of double-negatives.
    • additionally - we need the ability to use the underlying index to implement our Application Assertions.
      • APEX Validation
      • (compound) Trigger (per Oracle 7)

    BETTING POOL

    I see a strong correlation between Indexes, ORDCIIndex, and MATERIALIZE VIEW REFRESH FAST ON STATEMENT.

    Once a FAST REFRESH ON STATEMENT MV can handle aggregates and modifications on dimension tables, the ASSERT command should be made available that release, or soon after.

    After all, I see a CHECK Constriant on the underlying table of that type of MV as the way to implement an ASSERTION.

    I wouldn't doubt it if ASSERT was made available for the next release. But, it would make sense if they announce ASSERT's availability for OOW.

    Place your bets now.

    note: My Magic Oracle 8i ball says "answer not clear".

  • user6776382
    user6776382 Member Posts: 1 Red Ribbon

    A huge advantage - please make😊

  • User_77701
    User_77701 Posts: 1 Employee

    I'd definitely add my support to this - it provides tools to solve many problems I've seen in far more elegant fashion than they can currently be solved. I also agree that it's a hard feature to build correctly, with danger that assertions could easily be built that would greatly slow DML, while not necessarily being the obvious reason the DML is slow. (Of course, this is also true of the trigger-based work-arounds we already have that can also fire slow SQL in the background. It would be a useful accompanying feature to provide an easier way to see all cascading SQL that results from any given DML, together with its execution plans to address both the potential for new, slow assertions and the existing issue of slow trigger SQL.)

  • connor_mc_d-Oracle
    connor_mc_d-Oracle Posts: 88 Employee
    edited Nov 12, 2022 1:32PM

    SQL domains are coming in an upcoming release, and are a step along the path of the assertions journey.

    (Standard safe harbour applies, what you see in the 23c beta release may or may not end up production etc)

apps-infra