apps-infra

    Forum Stats

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

Discussions

SQL Assertions / Declarative multi-row constraints

145791029

Comments

  • doberkofler
    doberkofler Member Posts: 43 Blue Ribbon

    +1 as this would be an immensely important addition

  • Paul_______A.
    Paul_______A. Member Posts: 62 Blue Ribbon

    Hi Toon,

    Interesting idea. But I'm somewhat worried about possible performance implications, for instance during bulk inserts, imports and such.

    We may need an option to disable/enable the assertion to overcome such issue, although the enable assertion might take a long time in case of full table scans.

    Regards,
    Paul

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

    Hi Toon,

    Interesting idea. But I'm somewhat worried about possible performance implications, for instance during bulk inserts, imports and such.

    We may need an option to disable/enable the assertion to overcome such issue, although the enable assertion might take a long time in case of full table scans.

    Regards,
    Paul

    Paul, in other words it will not be different than the issues with indexes and constraints - and we will need to deal with it in the same way?

    In which case - what is new?

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

    Looks like we got our first down votes last night.... :-(

    I'd be interested to know the arguments.

  • Toon_Koppelaars-Oracle
    Toon_Koppelaars-Oracle Member Posts: 100 Employee
    edited May 27, 2016 5:01AM

    Hi Toon,

    Interesting idea. But I'm somewhat worried about possible performance implications, for instance during bulk inserts, imports and such.

    We may need an option to disable/enable the assertion to overcome such issue, although the enable assertion might take a long time in case of full table scans.

    Regards,
    Paul

    Yes the intention is to have the same options as we currently have with constraints:

    clauses.gif

    The RELY/NORELY will likely be a no-op, as I don't suspect we will have any semantic query optimization in the first release.

    Also the using_index_clause would not apply to a generic assertion case.

    Ruslan D.
  • User_RWJTS
    User_RWJTS Member Posts: 9 Blue Ribbon

    Lasted a long time.

    Toon_Koppelaars-OracleApexBine
  • Brian Tkatch
    Brian Tkatch Member Posts: 337
    edited May 26, 2016 8:27AM

    I think this is a terrible idea. It would add complexity to the database and would afford so many idiots so may new ways to destroy everything. Please, please, do not add this.

    When i say idiots, i mean people who just don't get it. That write ridiculous code like:

    BEGIN
                     SELECT NVL(MAX(XXX_BATCH_JOB_R),0) INTO nBatchNo FROM XXX_BATCH_JOB_CONTROL WHERE XXX_BATCH_JOB_N = sBatchName;
                 IF nBatchNo = 0 THEN
                     nBatchNo := 0;
                 END IF;

             EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                             nBatchNo := 0;
             END;
                   preBatchNo := nBatchNo;
                   nBatchNo := nBatchNo + 1;

    And then when using the variables:

    LPAD (NVL (preBatchNo,0),6,0) ||

    LPAD (NVL (nBatchNo,0),6,0)

    I just happened to have that example handy. But, there are so many examples, where people do so many idiotic things, you wonder how lucky you have been that the code somehow worked until now. Or what errors have happened that have not been noticed.

    Though, the idea of assertions is nice. A simple way to resolve AT_MOST_ONE_PRESIDENT is to add a UNIQUE new COLUMN GENERATED AS CASE Job WHEN 'President' THEN 1 END. This can get complex though, and more specific support would be nice. But if assertions were added, i would probably avoid them in favor of this simple approach. I'd rather use a statement than a query. That's no different than a TRIGGER.

    Can't multi-table check constraints can be done via MATERIALIZED VIEWs, in the same fashion? Why would an assertion be any better? Regardless, i don't like the approach. To me it seems that only a programmer would like that, not a designer. Assertions focus on what it does, similar to expecting EXCEPTIONS to fire to handle this or that case. I would rather the data be inherently constrained, such as, this COLUMN must be unique, or the like.

    NO_TRAINERS_IN_BOSTON is multi-table, but could be addressed if CONSTRAINTs could use FOREIGN KEYs to validate. That is, imagine CONSTRAINTs could refer to another TABLE's COLUMNs based on the REFERENCES clause. For one-to-one, or many-to-one, this would be simple, which is the case in the example. So, if EMP.DEPTNO were defined as REFERENCES DEPT, CONSTRAINT CHECK(NOT(EMP.JOB e.JOB = 'TRAINER' AND d.LOC = 'BOSTON')) should work.

    VACATION_DURING_EMPLOYMENT is similar, but backwards, as the CONSTRAINT ought to be on the EMP_VACATION table. Nonetheless, utting it on EMP, as above, would work the same way. That is, if EMP.EMPNO were defined as REFERENCES EMP_VACATION, CONSTRAINT CHECK(NOT(EMP_VACATION.FIRST_DATE < EMP.HIRE_DATE or EMP_VACATION.LAST_DATE  > EMP.TERMINATION_DATE)) would work.

    AT_LEAST_ONE_CLERK_PER_DEPT (why does it refer to DEPT?) is truly multi-row, and would require a multi-row CONSTRAINT. Allowing aggregations in a CONSTRAINT (for the COLUMN(s) being constrained) could work. CONSTRAINT CHECK(NOT (COUNT(CASE JOB WHEN 'CLERK' THEN 1 END) = 0 GROUP BY DEPTNO)). Though, that seems overly complicated. Another method would be to break it up via a new COLUMN GENERATED BY the CASE statement, and CONSTRAINT that COLUMN with the aggregate. (I have a feeling  there is a simpler way that escapes me right now.)

    AllPartSupp is a business rule, not a data rule, and ought not be in the data model.

    To summarize, i do not like assertions because they rely on what it does rather than what it is. To enable cross-table constraints, allow constraints to use foreign keys as if they were part of the same table. To enable multi-row constraints, perhaps allow aggregate functions.

    Stew AshtonD.Vega
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond

    I think this is a terrible idea. It would add complexity to the database and would afford so many idiots so may new ways to destroy everything. Please, please, do not add this.

    When i say idiots, i mean people who just don't get it. That write ridiculous code like:

    BEGIN
                     SELECT NVL(MAX(XXX_BATCH_JOB_R),0) INTO nBatchNo FROM XXX_BATCH_JOB_CONTROL WHERE XXX_BATCH_JOB_N = sBatchName;
                 IF nBatchNo = 0 THEN
                     nBatchNo := 0;
                 END IF;

             EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                             nBatchNo := 0;
             END;
                   preBatchNo := nBatchNo;
                   nBatchNo := nBatchNo + 1;

    And then when using the variables:

    LPAD (NVL (preBatchNo,0),6,0) ||

    LPAD (NVL (nBatchNo,0),6,0)

    I just happened to have that example handy. But, there are so many examples, where people do so many idiotic things, you wonder how lucky you have been that the code somehow worked until now. Or what errors have happened that have not been noticed.

    Though, the idea of assertions is nice. A simple way to resolve AT_MOST_ONE_PRESIDENT is to add a UNIQUE new COLUMN GENERATED AS CASE Job WHEN 'President' THEN 1 END. This can get complex though, and more specific support would be nice. But if assertions were added, i would probably avoid them in favor of this simple approach. I'd rather use a statement than a query. That's no different than a TRIGGER.

    Can't multi-table check constraints can be done via MATERIALIZED VIEWs, in the same fashion? Why would an assertion be any better? Regardless, i don't like the approach. To me it seems that only a programmer would like that, not a designer. Assertions focus on what it does, similar to expecting EXCEPTIONS to fire to handle this or that case. I would rather the data be inherently constrained, such as, this COLUMN must be unique, or the like.

    NO_TRAINERS_IN_BOSTON is multi-table, but could be addressed if CONSTRAINTs could use FOREIGN KEYs to validate. That is, imagine CONSTRAINTs could refer to another TABLE's COLUMNs based on the REFERENCES clause. For one-to-one, or many-to-one, this would be simple, which is the case in the example. So, if EMP.DEPTNO were defined as REFERENCES DEPT, CONSTRAINT CHECK(NOT(EMP.JOB e.JOB = 'TRAINER' AND d.LOC = 'BOSTON')) should work.

    VACATION_DURING_EMPLOYMENT is similar, but backwards, as the CONSTRAINT ought to be on the EMP_VACATION table. Nonetheless, utting it on EMP, as above, would work the same way. That is, if EMP.EMPNO were defined as REFERENCES EMP_VACATION, CONSTRAINT CHECK(NOT(EMP_VACATION.FIRST_DATE < EMP.HIRE_DATE or EMP_VACATION.LAST_DATE  > EMP.TERMINATION_DATE)) would work.

    AT_LEAST_ONE_CLERK_PER_DEPT (why does it refer to DEPT?) is truly multi-row, and would require a multi-row CONSTRAINT. Allowing aggregations in a CONSTRAINT (for the COLUMN(s) being constrained) could work. CONSTRAINT CHECK(NOT (COUNT(CASE JOB WHEN 'CLERK' THEN 1 END) = 0 GROUP BY DEPTNO)). Though, that seems overly complicated. Another method would be to break it up via a new COLUMN GENERATED BY the CASE statement, and CONSTRAINT that COLUMN with the aggregate. (I have a feeling  there is a simpler way that escapes me right now.)

    AllPartSupp is a business rule, not a data rule, and ought not be in the data model.

    To summarize, i do not like assertions because they rely on what it does rather than what it is. To enable cross-table constraints, allow constraints to use foreign keys as if they were part of the same table. To enable multi-row constraints, perhaps allow aggregate functions.

    If stupidity is the issue, then we need to also remove PL/SQL from the database - as a LOT of PL/SQL code is just plain stupid, not using SQL correctly, and doing instead in PL/SQL what should be done in SQL.

    But then stupid code in PL/SQL is not a PL/SQL language issue. It is lack-of-intelligence on the part of the developer issue.

    So argue the issue on logic and usability instead, and leave stupidity to be dealt with by lead pipe instead?

    EtbinAPCApexBineSentinel
  • Brian Tkatch
    Brian Tkatch Member Posts: 337
    edited May 26, 2016 9:29AM

    If stupidity is the issue, then we need to also remove PL/SQL from the database - as a LOT of PL/SQL code is just plain stupid, not using SQL correctly, and doing instead in PL/SQL what should be done in SQL.

    But then stupid code in PL/SQL is not a PL/SQL language issue. It is lack-of-intelligence on the part of the developer issue.

    So argue the issue on logic and usability instead, and leave stupidity to be dealt with by lead pipe instead?

    >If stupidity is the issue, then we need to also remove PL/SQL from the database

    That's a non-sequitur. One, PL/SQL is not part of the data model. Two, if there was another, simpler method with no cons, i might agree. But, there isn't, so we use what we have. Conversely, Assertions are not currently implemented and there are, arguably, better options.

    user5210021
  • Toon_Koppelaars-Oracle
    Toon_Koppelaars-Oracle Member Posts: 100 Employee
    edited May 27, 2016 5:31AM

    I think this is a terrible idea. It would add complexity to the database and would afford so many idiots so may new ways to destroy everything. Please, please, do not add this.

    When i say idiots, i mean people who just don't get it. That write ridiculous code like:

    BEGIN
                     SELECT NVL(MAX(XXX_BATCH_JOB_R),0) INTO nBatchNo FROM XXX_BATCH_JOB_CONTROL WHERE XXX_BATCH_JOB_N = sBatchName;
                 IF nBatchNo = 0 THEN
                     nBatchNo := 0;
                 END IF;

             EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                             nBatchNo := 0;
             END;
                   preBatchNo := nBatchNo;
                   nBatchNo := nBatchNo + 1;

    And then when using the variables:

    LPAD (NVL (preBatchNo,0),6,0) ||

    LPAD (NVL (nBatchNo,0),6,0)

    I just happened to have that example handy. But, there are so many examples, where people do so many idiotic things, you wonder how lucky you have been that the code somehow worked until now. Or what errors have happened that have not been noticed.

    Though, the idea of assertions is nice. A simple way to resolve AT_MOST_ONE_PRESIDENT is to add a UNIQUE new COLUMN GENERATED AS CASE Job WHEN 'President' THEN 1 END. This can get complex though, and more specific support would be nice. But if assertions were added, i would probably avoid them in favor of this simple approach. I'd rather use a statement than a query. That's no different than a TRIGGER.

    Can't multi-table check constraints can be done via MATERIALIZED VIEWs, in the same fashion? Why would an assertion be any better? Regardless, i don't like the approach. To me it seems that only a programmer would like that, not a designer. Assertions focus on what it does, similar to expecting EXCEPTIONS to fire to handle this or that case. I would rather the data be inherently constrained, such as, this COLUMN must be unique, or the like.

    NO_TRAINERS_IN_BOSTON is multi-table, but could be addressed if CONSTRAINTs could use FOREIGN KEYs to validate. That is, imagine CONSTRAINTs could refer to another TABLE's COLUMNs based on the REFERENCES clause. For one-to-one, or many-to-one, this would be simple, which is the case in the example. So, if EMP.DEPTNO were defined as REFERENCES DEPT, CONSTRAINT CHECK(NOT(EMP.JOB e.JOB = 'TRAINER' AND d.LOC = 'BOSTON')) should work.

    VACATION_DURING_EMPLOYMENT is similar, but backwards, as the CONSTRAINT ought to be on the EMP_VACATION table. Nonetheless, utting it on EMP, as above, would work the same way. That is, if EMP.EMPNO were defined as REFERENCES EMP_VACATION, CONSTRAINT CHECK(NOT(EMP_VACATION.FIRST_DATE < EMP.HIRE_DATE or EMP_VACATION.LAST_DATE  > EMP.TERMINATION_DATE)) would work.

    AT_LEAST_ONE_CLERK_PER_DEPT (why does it refer to DEPT?) is truly multi-row, and would require a multi-row CONSTRAINT. Allowing aggregations in a CONSTRAINT (for the COLUMN(s) being constrained) could work. CONSTRAINT CHECK(NOT (COUNT(CASE JOB WHEN 'CLERK' THEN 1 END) = 0 GROUP BY DEPTNO)). Though, that seems overly complicated. Another method would be to break it up via a new COLUMN GENERATED BY the CASE statement, and CONSTRAINT that COLUMN with the aggregate. (I have a feeling  there is a simpler way that escapes me right now.)

    AllPartSupp is a business rule, not a data rule, and ought not be in the data model.

    To summarize, i do not like assertions because they rely on what it does rather than what it is. To enable cross-table constraints, allow constraints to use foreign keys as if they were part of the same table. To enable multi-row constraints, perhaps allow aggregate functions.

    ... and would afford so many idiots so may new ways to destroy everything.
    
    

    Your post warrants many replies. But let's start with this one.

    What would/could they possibly be destroying by declaring additional constraints via assertions?

apps-infra