Forum Stats

  • 3,728,023 Users
  • 2,245,521 Discussions
  • 7,853,249 Comments

Discussions

SQL Assertions / Declarative multi-row constraints

Toon_Koppelaars-Oracle
Toon_Koppelaars-Oracle Member Posts: 98 Employee
edited November 2017 in Database Ideas - Ideas

[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.

Toon_Koppelaars-OracleAlex NuijtenMartin Giffy D'SouzaRob van WijkPatch72Nimish GargKim Berg HansenLucas JellemaGalo BaldaJonathan LewispattonjgIggy Fernandez3133490Christian Neumueller-OracleErik van RoonPatrick Wolf-OracleApexBinePeter GrambarsemaTobianoDaniel Hochleitneruser10205865user5237836MortenBratenuser12238076William RobertsonJeffrey KempBryn.Llewellyn-Oracleuser11975024Mikhail VelikikhNikolaus HorntrentFranck Pachotpeter_raganitschconnor_mc_d-OracleGert Poeljoeyeno3241855MKJ10930279Stew Ashtonuser4328259jbosman3035759jurgenk3022681Johnnq72BeGinM_SquareSteven Davelaar-OracleAlan ArentsenTorsten KleiberKevan Gellinguser456779PeterVanRijnTonieDf60104e1-1c47-4296-8dbc-dcfc1dd2f8c4Bruno CironeGerard Groen-OracleDennis Schafthuizen-OracleJW_Veldhuis-OracleChris Saxon-Oracle1920519ctriebKees Oskam-Oracleharshakb-Oraclecmartin2user11979134BvdrHorst-OracleMitja GolouhDevaRaju Thotacommi235rooimPhilipp Salvisberguser6492986user2409631user5499834Andreas BuckenhoferUser_CCJ66Micke VrolijkFrank Tollenaar-OracleUser13259034-OracleChris Antognini158860Edward OrlowskiDave SkillBuildersSstacey-Oracletim_evdbtDrabJayuser10944364Mettemusens2Martin Preisstkyte-OracleMarinaPKent Grazianouser12000573ajallenhatmaniChrisAyre28132001188730Arian Stijfuser10213881pietervpschot2983299Peter EralyRandolf GeistNick Buytaert2834237fac586User_8DQK2Niall Litchfield1920959user1740962berxuser9045295user10385563Robert LockardIlmar KermMautro PaganoTobiasEstampeJeremy SchneiderRumpiEtbinRaj Jamadagniantonm12206551483608andrevwinsspudgeAdricB.Thomasuser4918322BrianPaLuis Claudio SantosHeath Sheehan2605820NSK2KSNnajirkN.B.user10391064Gus.SpierUlfHans ForbrichJohn ThomasBabak TouraniJorge RimblasRafu2794957Marko Huser11991904HeliMenno Hoogendijkadc39258-4b98-4709-98a8-65591ec16e29ParsAlex Hafneruser1197931253216192-3299-44ee-8727-bba9ccfb5560user1077573user513485AbuFazalPeTofFinlanduser9342015TimppaGffeee67f-4c83-4e87-9885-a72403f57f761b69eeae-8332-43b0-beba-055ea2f7d304Marc FieldingRob van den Bergjakobhjeric_grancheruser7904656Tom vd DuinvheiHenk JanJarmo Alatalo1791382user7124841user9958516Heikki S.16102822649015Niklas STeijo LUser_U1CQTuser4912260user7164590andres31GregVuser2813197KayKAman....Billy Verreynneuser12271812SUPRIYO DEYtoonieuser4524251481758dba_mmSven W.paddersMatthiasRogelFatMartinRToineuser7048955user489938BPeaslandDBAMikeV53Uwe Küchler [OC]Ondrej KluckadthrashtaconecomjgarryHemant K Chitale2783224user7010513Rajeshwaran, JeyabalRoel Hartmanuser615230-Oracleangelo.stramieriUser_KZJKD3244562Freek D'HoogeWouter de Waarduser9944999wgkwvl2Elias Nemausman_noshahibrianray306416330158511292799050908d5-9acf-4b39-a4dd-98fb2a7868dc29ff6c77-67e6-403b-a7a4-f2437b860aacMurray SobolAndreyNuser12058618user11093747user10714229user1885282Kris JohnstonJuliusZRuslan D.user1561316Porus Homi Havewala, OCMRobbert Serne1025411Thomas DonathAdrian D17331751254358user10218349Karthick2003user9183611EricaHarrise44ab2fb-4467-4456-b068-5f3b78db6f7dDerek-LdtranjulsVic010Pranay DeepUser_8YS7Luser13469560Erik Trip - Darwin IT2967680user12026701Steve CheslerPablo Escudero3245842user566379ManishHathiAamir8143183123Shannon Severancechristoph.leserSean ZechmanBruno Carvalhouser4531167qaiserdbaOnTapypapa324636434467711-254a-44d8-8562-94db7760014f154602419473811926671Oscar de la Torre-OracleC. Maßnick1449766user8696402JasonInVegasUser_835IIAnand Yadavuser2848802Craig ElliottUser_S1LHLgaverillTexasApexDeveloperAPCfaviantorresMNituMeeuwtjeLaurens WagemakersZohar ElkayamMarco Mischkeziehmeruser10940810kulikouskiRichard SmithEdwin van MeerendonkrvstuckeZlatko SiroticMakiZoran PavlovicDavidMcWhinnie3220967Vin Steele1MarkusHohlochFarzad SoltaniKOXyelkaramaGuna Challa2743343glenmPavel KhromovSeánMacGCuser9224311user3637254User_FLNLB1095492Peter.Nuser12234405Hari_639theoa2d2d5998-8536-4ec1-aba8-e5b9537bddc8Andre SantosKamilZ3178676User_UVNOAgulbrainuser11198823user9177712paddyoneA. Olsenuser106232471771878ThoreauBJones1423717Andrew Nicholls26368173249559c006e45e-ddf0-403a-8694-d6b268d8fa48user3388198EuanMcViej*c*m*s*h*vtonibony73034114user4164360kishore339&amp;#26408;&amp;#21280;Charlie1063231user13364405946099user61925745c6e4cec-787c-4a56-a5ea-4a1afce715d1877522Chris MentenKamal KishoregkbMark J. Bobak, NITSLothar FlatzDenny K1169597hermanmolnainostreboruser12221703user6782495Arup Nanda_2d81500d9-3498-47f8-892c-b77b83c6c7edstefan jordanov1483588DavidPykelenzinJohn StegemanPaoloMTom B.919723c5-1fab-4510-a20e-08cbda8b599e2738712Beilstwhuser12138705jflackuser8277478jremacle26726e9a-ad81-4159-8596-dd053e07caebdavemmjemd01Jagdeep SangwanChristian ErlingerBluShadowbinsoftuser41126721098339-W.FlavioFreitasMarwimzOuJ.SchnackenbergSalavat Bogdanov2687753Learco Brizzi3066685tanmoydcuser4350379MartinKlierDBARobert Podtmiguellima31771091061562Vysakh Suresh - 3035408AllenAbrahamUser_9D2BCUser797676-Oracleuser7685703Ramon Caballero EscamillaPhGUser_2PLWCSaibabu_Devabhaktuni-KE-2972256Jon TheriaultjkstillJanuary Tabaka-Oracle2678845Oyvind Isene1782296Russ Flemingananthkrk28794512617948Oleh Tyshchenkoparthibannuser5252257Phil Clifforda59290d0-b3e2-4476-812b-dd9c5c4d2b91user11993906user7328662GinoThysuser10889721Brian Camire3241488Konstantin MylnikovtheHOrsepettejaJonathanW960326miroslawWvdHoornsensoftVivek L3259943Mahir M. QuluzadeCloudDBuser6353874VJMAparna Dutta-OraclemUday-Oracleuser943139HalldorStefan Koehlerjnicholas330Robin BuitenhuisMatheus Boesingtkiernanuser54425172603832dtspisakGbenga AjakayeLudovicoCaldaraNoname123nohupUser_67QPLStewartStevensToddBarryuser12048529GKrisuser7923359haarseMikeLBrownDejan T.Daniel E.Donald BUser_1TGCVHoekMTouny741405br0zikRichard SouleMathias Magnussonuser78315082712425Chris HolliesUser_EV7JQOren NakdimonVadim Tropashko-OracleUser_N9EK3PDVBVRavindraChDani Schniderazathoth11dherzhauUser_C20E8Niels HeckergdsotirovThomas Ranchonuser13423947Lasse Jenssen2731771ahgonzalezRichard Harrison .1037293Sri Annamuser3671046David KyanekDavevaeruser11974664Kevin van DijkManik3006369user12047985Dallas Deeds1244776user12167459Lukas EderThomas Teske-Oracle3224108user3609240alataiJohn_KsdstuberBhavesh Tinna-OracleulohmannDavid Krch-Oracle32222673461955Dick DralMorten EganRobert MarzChrisDeBerguser10725729TRONd70jczuprynskiK Cannell, TH Tech3397048KevinAngusAnnelizeF49fd541f-5cea-4617-86df-2e2e3261cf43a_rintoul3136811Mark CasazzamathguyKofi1056364Peter HraškoAndy WeissJustin Warwick3485149Thorsten KettnerSam_PBEDEjbbarretoAnderson GonçaloLauryJ. Fuda-Oraclesbird-Oraclepnoskojohnnie.billingsa22fdaa1-4bb2-4162-97ca-96748f81f3ddAnthony EvansEmad Al-Mousauser11197070Mustafa_KALAYCIblamashachou1434Sebastien A.Danilo PiazzalungaEricKR-OracleAlbert Nelson AYunus-OraclePeter GjelstrupNextNameblessed DBA3837b9ff-0b94-4e3f-9c42-ef7a10f59be83583506Goran Hbrandon@famoussteve.moore@polk.co.ukKnut Härteld9740e4a-26f9-4aba-8ce9-4d073c2965b4user2740009Evandro Lima-OracleEdgar Chupituser560554Mahmoud_Rabie856392Tobias B.Jairo SuarezfmzRaymond APiotr WrzosekUser_AY8YYuser10273200aakif666645user3840601H_PaulineNibaluser4377404Alex Lamar-Oracleca20c7a9-c48d-4d28-a45a-dbbe6828aca1puchtecGünterUser798041-Oracle3811321user9526860BrunoVromanuser5878130lars.johan[at]ulveseth.compchircopMWRA EnQualBoneistSamuel NitscheMarkvU9607969288f416-a43e-4a9a-98ed-ecfac2387cafErwin VrielinkJedS21122KGelling2d283f715-4e02-49fa-ab0c-f01159fba58369af3d9c-ec05-4210-a884-935987f2fb55Lunaboltuser9376047Dave SchleisLoïc Lefèvre-OracleUser_CNT03philippe_eSentinelL. FernigriniUser_2KT88unficypuser11987530User_70H3Cpmdbarober584812user6776382User_9GEWMuser10285user10456451BartCernJazzhopMarcel HoefsTony AndrewsBlaisuser11970842
673 votes

Active · Last Updated

«13456711

Comments

  • Martin Giffy D'Souza
    Martin Giffy D'Souza Member Posts: 484

    +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.

    ApexBineSebastien A.
  • Toon_Koppelaars-Oracle
    Toon_Koppelaars-Oracle Member Posts: 98 Employee
    edited May 2016

    +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
    Nimish Garg Member Posts: 3,185 Gold Trophy
  • Kim Berg Hansen
    Kim Berg Hansen Senior System Developer Member Posts: 999 Bronze Trophy

    +1 ain't enough - +42 at least

    JW_Veldhuis-OracleApexBineDuke Ganotetheoa
  • Lucas Jellema
    Lucas Jellema Member Posts: 327

    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

    Toon_Koppelaars-Oracle
  • Erik van Roon
    Erik van Roon Member Posts: 57 Red Ribbon

    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?

    Thorsten Kettner
  • Toon_Koppelaars-Oracle
    Toon_Koppelaars-Oracle Member Posts: 98 Employee

    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
    Erik van Roon Member Posts: 57 Red Ribbon

    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
    Jeffrey Kemp Member Posts: 193 Red Ribbon

    +1000 make it so, please

  • I wholeheartedly support this.

    William RobertsonpudgeIggy Fernandez
  • Gemma
    Gemma Member Posts: 5 Blue Ribbon

    Fantastic idea!

    Toon_Koppelaars-Oracle
  • Nikolaus Horn
    Nikolaus Horn Member Posts: 1 Blue Ribbon

    Great Idea! My Use-Case would be overlapping Date constraints.

    user5210021
  • connor_mc_d-Oracle
    connor_mc_d-Oracle Posts: 65 Employee

    The reduction is application code alone justifies the incredible potential here.

    pudge
  • M_Square
    M_Square Member Posts: 1

    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!

    Toon_Koppelaars-Oracleberx
  • Toon_Koppelaars-Oracle
    Toon_Koppelaars-Oracle Member Posts: 98 Employee

    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!

    ;-)

  • William Robertson
    William Robertson Member Posts: 9,560 Bronze Crown
    edited May 2016

    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.

    ApexBineAllenAbraham
  • user5499834
    user5499834 Member Posts: 2

    Splendid idea. Solves a big omission in data integrity constraints

  • Dave SkillBuilders
    Dave SkillBuilders Member Posts: 37 Blue Ribbon
  • JohnWatson2
    JohnWatson2 Member Posts: 4,226 Bronze Crown

    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

  • Toon_Koppelaars-Oracle
    Toon_Koppelaars-Oracle Member Posts: 98 Employee

    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.

    Jeffrey KemppudgeberxErwinSmout
  • JohnWatson2
    JohnWatson2 Member Posts: 4,226 Bronze Crown

    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

  • 2813200
    2813200 Member Posts: 1

    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.

  • Mike Kutz
    Mike Kutz Member Posts: 5,588 Silver Crown

    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).

Sign In or Register to comment.