SQL Assertions / Declarative multi-row constraints

Toon_Koppelaars-Oracle

[edit: ^^ Grey vote-up/down arrows will appear after login to OTN (create account here: https://profile.oracle.com/myprofile/account/create-account.jspx) ^^]

We are considering building support for the CREATE ASSERTION command in a next release of the Oracle database. Assertions have been part of the SQL standard since SQL-92. You can find the BNF definition for SQL assertions here: https://github.com/ronsavage/SQL/blob/master/sql-92.bnf  (search for "assertion definition").

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. Support request for SQL assertions has come up on Asktom several times:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:21389386132607

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:4233459000346171405

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:698031000346429496

Any arbitrary (static) constraint can be specified as a SQL assertion. With support for SQL assertions, there would be no longer a need to build the notoriously complex and error-prone database triggers for cross-row constraints. Once a SQL assertion has been declared to the RDBMS, it is the task of the RDBMS to ensure its continued validity during transactions that change the involved tables/columns.

A few examples.

This SQL statement creates an assertion to demand that there's no more than a single president among the employees:

create assertion AT_MOST_ONE_PRESIDENT as CHECK
((select count(*)

    from EMP e

   where e.JOB = 'PRESIDENT') <= 1

)

This SQL statement creates an assertion to demand that Boston based departments do not employ trainers:

create assertion NO_TRAINERS_IN_BOSTON as CHECK

   (not exists

    (select 'trainer in Boston'

       from EMP e, DEPT d

      where e.DEPTNO = d.DEPTNO

        and e.JOB    = 'TRAINER'

        and d.LOC    = 'BOSTON')

   )

This SQL statement creates an assertion to demand that vacation records cannot be outside of one's employment period:

create assertion VACATION_DURING_EMPLOYMENT as CHECK
(not exists

     (select 'vacation outside employment'

        from EMP e

            ,EMP_VACATION ev

       where e.EMPNO = ev.EMPNO

         and (ev.FIRST_DATE < e.HIRE_DATE or

              ev.LAST_DATE  > e.TERMINATION_DATE))

   )

This SQL statement creates an assertion to demand that every department employs a clerk:

create assertion AT_LEAST_ONE_CLERK_PER_DEPT as CHECK
(not exists

        (select 'a department without a clerk'

           from DEPT d

          where not exists

              (select 'a clerk in d'

                 from EMP e

                where e.DEPTNO = d.DEPTNO

                  and e.JOB    = 'CLERK'))

    ) deferrable initially deferred

This SQL statement creates an assertion to disallow suppliers based in cities of Albany, Palo Alto, or Portland from supplying, in quantities higher than 50, all the parts that are red or cost $10.00 or more:

create assertion AllPartSupp as CHECK

   (not exists

     (select 'an s shipping all parts'

        from SUPPLIER s

       where s.CITY in ('Albany', 'Palo Alto', 'Portland')

         and not exists

               (select 'a p not shipped'

                  from PART p

                 where (p.COLOR = 'red' or p.PRICE >= 10)

                   and not exists

                        (select 'a connecting sh'

                           from SHIPMENT sh

                          where sh.QUANTITY > 50

                            and sh.SNO = s.SNO

                            and sh.PNO = p.PNO)))

   )

The implementation of SQL assertions would be such that only when a transaction changes involved data in such a manner that it could potentially violate the SQL assertion, would the RDBMS perform a re-validation. For instance, the first example above concerning the number of presidents, would not be revalidated on insert of a CLERK, as the SQL assertion is immune to this kind of insert. Furthermore, whenever this is possible, the RDBMS would perform a delta-check and not reevaluate the whole SQL assertion expression. For instance, for the second example above concerning the trainers not allowed in Boston, the RDBMS would only revalidate department, say 10, if a trainer were to be inserted into department 10.

As can be seen in the fourth example above, SQL assertions could be defined such that semantically the revalidation takes place at the end of the transaction, thereby allowing temporary violations during a transaction. Such support for SQL assertions would also undo the need for a before-commit trigger, which has been proposed here:

All the familiar attributes that are currently available for declarative constraints, such as rely/norely, enable/disable, validate/novalidate and the exceptions-into clause, are under consideration to support.

Please let us know whether you would like to have support for SQL assertions in the future.

Martin Giffy D'Souza

+1 great idea. Have need this for a long time specifically for overlapping date constraints that cross multiple rows and tables.

Regarding the Before Commit trigger. When would this validation occur? Before the commit, on DML, or configurable? I can see the need for both options though it may get confusing for developers.

Toon_Koppelaars-Oracle

+1 great idea. Have need this for a long time specifically for overlapping date constraints that cross multiple rows and tables.

Regarding the Before Commit trigger. When would this validation occur? Before the commit, on DML, or configurable? I can see the need for both options though it may get confusing for developers.

Martin,

It would be configurable, just like it currently is for declarative constraints. You would be able to specify the 'deferrable', 'not deferrable', 'initially deferred', etc.

I demonstrated that with the fourth example.

Toon

Nimish Garg

+1

Kim Berg Hansen

+1 ain't enough - +42 at least

Lucas Jellema

Hi Toon,

I have been involved in some of the discussions in the past about before-commit triggers as a way to implement transaction level, cross record constraints - beyond foreign and unique keys. The notion of the SQL Assertions is far more elegant. I wholeheartedly support this proposal - it would be a major enhancement to the Oracle Database and help positioning it against other databases with ACID but poorer support for data integrity. It would also far improve the ability for current Oracle Database customers to enforce true data integrity - which is not easy at the moment, if feasible at all.

kind regards,

Lucas

Erik van Roon

Yes, Toon, please!

And Yesterday, please!

But, one question:

Wouldn't such assertions always need to be deferred?

In case of first example:

user 1 inserts a president

user 2 inserts a president

user 2 commits

user 1 rollbacks.

I think in above scenario the assertion shouldn't bother anyone with the existence of 2 presidents.

In fact, if it does user 2 will go nuts trying to find out why the database thinks there is more than 1 president.

Or am I missing something?

Iggy Fernandez

The creator of the relational model, Dr. Codd, touted its simplicity and consequent appeal to users—especially casual users—who have little or no training in programming. He singles out this advantage in the opening sentence of his first paper on relational theory A Relational Model of Data for Large Shared Data Banks: “Future users of large data banks must be protected from having to know how the data is organized in the machine (the internal representation).” He made the point more forcefully in a subsequent paper, Normalized Data Base Structure: A Brief Tutorial in which he says: “In the choice of logical data structures that a system is to support, there is one consideration of absolutely paramount importance—and that is the convenience of the majority of users. … To make formatted data bases readily accessible to users (especially casual users) who have little or no training in programming we must provide the simplest possible data structures and almost natural language. … What could be a simpler, more universally needed, and more universally understood data structure than a table? Why not permit such users to view all the data in a data base in a tabular way?”

But the true importance of the relational model is highlighted by the title Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks of the unpublished original—and shorter—version of Dr. Codd’s paper which predated the published version by a year. That title hints that the chief advantage of the relational model is the ability it gives us to assert arbitrarily-complex consistency constraints that must be satisfied by the data within the database; that is, the ability to put the “C” into “ACID.” An example of a complex constraint is: A pilot may fly a certain type of aircraft only if (1) he or she has flown that type of aircraft previously or (2a) he or she has attended a training class on flying that type of aircraft and (2b) the instructor of that class is one of the co-pilots.

Oracle Rdb for the OpenVMS operating system already supports the SQL-92 CREATE ASSERTION specification (http://community.hpe.com/hpeb/attachments/hpeb/itrc-149/22979/1/15667.doc) so why not Oracle Database itself? Let’s put the “C” into “ACID.” We’ve waited for 25 years but better late than never.

Toon_Koppelaars-Oracle

Yes, Toon, please!

And Yesterday, please!

But, one question:

Wouldn't such assertions always need to be deferred?

In case of first example:

user 1 inserts a president

user 2 inserts a president

user 2 commits

user 1 rollbacks.

I think in above scenario the assertion shouldn't bother anyone with the existence of 2 presidents.

In fact, if it does user 2 will go nuts trying to find out why the database thinks there is more than 1 president.

Or am I missing something?

I had not mentioned it, as this becomes rather complex, but just as with foreign-key constraints, a proper serialization scheme will have to be applied inside transactions that affect SQL assertions. In you example above, the implementation would block the insert of user 2 and await either a commit or rollback from user 1 before proceeding.

Erik van Roon

I had not mentioned it, as this becomes rather complex, but just as with foreign-key constraints, a proper serialization scheme will have to be applied inside transactions that affect SQL assertions. In you example above, the implementation would block the insert of user 2 and await either a commit or rollback from user 1 before proceeding.

Ah, I see.

So you basically get a kind of a lock while doing an insert.

Thanks.

Jeffrey Kemp

+1000 make it so, please