- 3,728,023 Users
- 2,245,521 Discussions
- 7,853,249 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
- 17.8K All Categories
- Industry Applications
- 3.2K Intelligent Advisor
- Insurance
- 1.7K On-Premises Infrastructure
- 592 Analytics Software
- 51 Application Development Software
- 1.8K Cloud Platform
- 700.5K Database Software
- 17.5K Enterprise Manager
- 22 Hardware
- 276 Infrastructure Software
- 142 Integration
- 75 Security Software
SQL Assertions / Declarative multi-row constraints
[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.
Comments
-
+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.
-
+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
-
-
-
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
-
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?
-
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.
-
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.
-
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.
-
+1000 make it so, please
-
-
-
Great Idea! My Use-Case would be overlapping Date constraints.
-
The reduction is application code alone justifies the incredible potential here.
-
I have attended Toon's courses on this and related SQL matters - the mathematical elegance, and logistic simplicity is compelling. Oh yes, the internal code will be difficult, the potential for bugs plentiful, and reading execution plans and waits for "simple inserts" suddenly becomes a new challenge, but it is worth it. There is a reason why it is part of the SQL standard!
-
I have attended Toon's courses on this and related SQL matters - the mathematical elegance, and logistic simplicity is compelling. Oh yes, the internal code will be difficult, the potential for bugs plentiful, and reading execution plans and waits for "simple inserts" suddenly becomes a new challenge, but it is worth it. There is a reason why it is part of the SQL standard!
Hear, hear!
;-)
-
This could be a great feature and a unique selling point for Oracle. Let's hope it becomes a core feature and not another chargeable extra.
-
-
Splendid idea. Solves a big omission in data integrity constraints
-
-
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
-
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
Which one should throw the error? Neither one.
If you do a set-based insert, like in your example, it's the insert that fails, not one of the rows. Set-based inserts, conceptually, do not have an order of insert of the rows. And thus the implementation should (and will) be immune to this. The AT_MOST_ONE_PRESIDENT, is an example where the, let's call it, level-of-violation, cannot be mapped to a specific row in the table. We've been brought up with primary keys and foreign keys as the only declarative multi-row constraints, and in those two cases, coincidentally there is a one-on-one mapping from level-of-violation, to actual rows in the table. But in the generic case this certainly need not be, as you've just pointed out.
The proposed solution handles mutating tables. And it is able to deal with more complex situations.
Really, not really difficult.
Not easy, yes. Big project, yes. But do-able.
-
Which one should throw the error? Neither one.
If you do a set-based insert, like in your example, it's the insert that fails, not one of the rows. Set-based inserts, conceptually, do not have an order of insert of the rows. And thus the implementation should (and will) be immune to this. The AT_MOST_ONE_PRESIDENT, is an example where the, let's call it, level-of-violation, cannot be mapped to a specific row in the table. We've been brought up with primary keys and foreign keys as the only declarative multi-row constraints, and in those two cases, coincidentally there is a one-on-one mapping from level-of-violation, to actual rows in the table. But in the generic case this certainly need not be, as you've just pointed out.
The proposed solution handles mutating tables. And it is able to deal with more complex situations.
Really, not really difficult.
Not easy, yes. Big project, yes. But do-able.
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
-
This would be a major differentiating point between Oracle and other SQL DBMS on the market today. It also fits in perfectly with APEX in creating thick DB applications which can truly enforce business rules in the database independent of any application. A huge up vote from me.
I would think supporting assertions would be orthogonal to the rules manager, in that it seems to me the rules manager is intended for programming *action* oriented business rules (for example deciding into which department to place the new employee) whereas assertions are focused on *static* oriented business rules ensuring the database remains consistent with the data integrity rules stated to it after any transition from one database state to another is complete (for example verifying that the employees in each department are consistent with the rules defined about what kind of employees can be in what departments). To me they are distinct and complementary capabilities.
-
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 seems to be a way to develop a large IF/ELSE block for your BEFORE INSERT trigger.
I do not see it as a way to enforce "at most, 1 manager per DEPTNO".
(note - i have not used Rules Manager).
