For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
+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
+1
+1 ain't enough - +42 at least
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 presidentuser 2 inserts a presidentuser 2 commitsuser 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.
Ah, I see.
So you basically get a kind of a lock while doing an insert.
Thanks.
+1000 make it so, please
I wholeheartedly support this.
Fantastic idea!
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!
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.
Spe
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 WatsonOracle 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).
Perhaps it might be helpful to point out that these sorts of problems are *already being solved* out in the wild, using techniques like triggers with smart locking strategies, or strategic materialized views with constraints.
The difference here is that instead of requiring the developers to build these solutions "by hand" - which leads to exactly the situation you wish to avoid - "wide open to abuse and a dreadful capability for writing bug-ridden code"; that support be built into the Oracle database engine - i.e. problem solved once and for all, re-usable by all.
I have no doubt that the initial version that supports this will have some cracks and bugs that will need to be addressed (especially when combined in novel ways with other Oracle features). We all have come to accept the CBO, for example, but I remember a time when common wisdom was to stay with the RBO because the CBO was considered too new, experimental and bug-prone.
"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."
I won't add to Toon's response; but I will point out that you've just described the big problem with people using the "log errors" and "bulk exceptions" clauses.
Regards
Jonathan Lewis
+1 - about time too. Even (especially) where application logic is defined externally to the database ("thin database applications") the more the database protects itself against inconsistent data, the happier the business and its auditors can be.
Will try not to retire until then.
Etbin
"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." I won't add to Toon's response; but I will point out that you've just described the big problem with people using the "log errors" and "bulk exceptions" clauses.RegardsJonathan Lewis
Well said, both Toons and JL. Log errors and bulk exceptions are best reserved for data cleaning and translating operations into places that are not for end user representations of the truth such as (meaning one example of use) targets for partition exchange when the data is correct. I mention this because I believe it is the case of using an ill advised shortcut to slap questionable data in front of users, not the features themselves, to which you object. And of course data thus cleaned would complete the insert (or fail due a correctly constraining change to the production target) when added to production through a bulk insert without errors clauses. If an error then ensues, the process has already been defined to clean again, so we still get a correct result as efficiently as before but with the window for a constraint violation closed.
This feature has been deferred for a couple decades due to the weighted truths of 1) machines not being fast enough yet and 2) more pressing needs for feature improvement trumped this feature, and never because it was a "bad" feature. In the burgeoning era of "big data" that often is only a sample that needs only to be statistically valid, completing this feature in the Oracle RDBMS part of the technology stack will further improve the possibility of developers efficiently building applications that deliver precisely true data that adhere the designed constrains. When precise truth rather than mere statistical representation of trends is the need, this seems clearly to be the very best next step in progress toward that goal. +42.
Rules Manager cannot guarantee data integrity because (1) readers do not acquire read locks and hence do not block writers in Oracle Database; and (2) table triggers in Oracle Database can run into the "mutating table" problem.
re: dreadful capability for writing bug-ridden code.
According to the proposal, the only code that application developers and database administrators would have to write write is CREATE ASSERTION statements.
This is one of the excellent features that should be there for sure
+1 Great IDEA!
Very useful
+1 needed this feature multiple times before too, so I support this enhancement
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
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
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.
Aman....
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.
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.
Someone start a sticky titled 'Why can't I do an autonomous transaction inside my assertion'.
Billy~Verreynne wrote: The implementation would be interesting - and likely complex.
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
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).
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 ...
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 ;-)
Ah, so that's why Mark and Safra got kicked upstairs. No assertion for CEO.
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
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.
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.
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 SQLselect * 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.02MK
Definitely not a teaser...