Forum Stats

  • 3,853,216 Users
  • 2,264,194 Discussions
  • 7,905,288 Comments

Discussions

SQL Assertions / Declarative multi-row constraints

Toon_Koppelaars-Oracle
Toon_Koppelaars-Oracle Member Posts: 100 Employee
edited Dec 13, 2021 2:59PM in Database Ideas - Ideas

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 FernandezUser_ZJBSZChristian Neumueller-OracleErik van RoonPatrick Wolf-OracleApexBinePeter GramBarsemaTobianoDaniel Hochleitneruser10205865user5237836MortenBratenuser12238076William RobertsonJeffrey KempBryn.Llewellyn-Oracleuser11975024Mikhail VelikikhNikolaus HorntrentFranck Pachotpeter_raganitschconnor_mc_d-OracleGert PoelUser_BZXCP3241855MKJ10930279Stew Ashtonuser4328259jbosman3035759jurgenk3022681Johnnq72BeGinM_SquareSteven Davelaar-OracleAlan ArentsenKevan Gellinguser456779PeterVanRijnTonieDf60104e1-1c47-4296-8dbc-dcfc1dd2f8c4Bruno CironeGerard Groen-OracleDennis Schafthuizen-OracleJW_Veldhuis-OracleChris Saxon-Oracle1920519ctriebharshakb-Oraclecmartin2user11979134BvdrHorst-OracleMitja GolouhDevaRaju Thotacommi235rooimPhilipp Salvisberguser6492986user2409631user5499834Andreas BuckenhoferUser_CCJ66Frank Tollenaar-OracleUser13259034-OracleChris AntogniniEdward OrlowskiDave SkillBuildersSstacey-Oracletim_evdbtDrabJayuser10944364Mettemusens2Martin Preisstkyte-OracleMarinaPKent Grazianouser12000573ajallenhatmaniChrisAyre1188730Arian Stijfuser10213881pietervpschot2983299Peter EralyNick Buytaert2834237fac586User_8DQK21920959user1740962berxuser9045295user10385563Robert LockardIlmar KermMautro PaganoTobiasEstampeJeremy SchneiderEtbinRaj Jamadagniantonm1483608andrevwinsspudgeAdricB.Thomasuser4918322BrianPaLuis Claudio SantosHeath Sheehan2605820NSK2KSNnajirkN.B.user10391064Gus.SpierUlfHans ForbrichJohn ThomasBabak TouraniJorge RimblasRafu2794957Marko Huser11991904HeliMenno Hoogendijkadc39258-4b98-4709-98a8-65591ec16e29ParsAlex Hafneruser1197931253216192-3299-44ee-8727-bba9ccfb5560user1077573AbuFazalPeTofFinlanduser9342015TimppaGffeee67f-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.stramieri3244562Freek D'HoogeWouter de Waarduser9944999wgkwvl2Elias Nemausman_noshahibrianray30641633015851User_8BBSM050908d5-9acf-4b39-a4dd-98fb2a7868dc29ff6c77-67e6-403b-a7a4-f2437b860aacMurray SobolAndreyNuser12058618user11093747user10714229user1885282Kris JohnstonJuliusZRuslan D.user1561316Porus Homi Havewala, OCMRobbert Serne1025411Thomas DonathAdrian D17331751254358user10218349Karthick2003user9183611e44ab2fb-4467-4456-b068-5f3b78db6f7dDerek-LdoberkofleranjulsVic010Pranay DeepUser_8YS7Luser13469560Erik Trip - Darwin IT2967680Steve CheslerPablo Escudero3245842user566379Aamir8143183123Shannon Severancechristoph.leserSean ZechmanBruno Carvalhouser4531167qaiserdbaOnTapypapa324636434467711-254a-44d8-8562-94db7760014fUser_MPSJT1947381User_RWJTSC. Maßnick1449766user8696402JasonInVegasUser_835IIAnand Yadavuser2848802Craig ElliottUser_S1LHLgaverillTexasApexDeveloperAPCfaviantorresMNituMeeuwtjeLaurens WagemakersZohar ElkayamMarco Mischkeziehmeruser10940810kulikouskiRichard SmithEdwin van MeerendonkrvstuckeZlatko SiroticMakiZoran PavlovicDavidMcWhinnie3220967Vin Steele1MarkusHohlochKOXyelkaramaGuna Challa2743343glenmPavel Khromovuser9224311user3637254User_FLNLB1095492Peter.Nuser12234405Hari_639theoa2d2d5998-8536-4ec1-aba8-e5b9537bddc8Andre SantosKamilZ3178676User_UVNOAgulbrainuser9177712paddyoneuser106232471771878ThoreauBJones1423717Andrew Nicholls26368173249559c006e45e-ddf0-403a-8694-d6b268d8fa48user3388198EuanMcViej*c*m*s*h*vtonibony73034114user4164360kishore339&amp;#26408;&amp;#21280;Charlie1063231user13364405946099user61925745c6e4cec-787c-4a56-a5ea-4a1afce715d1877522Chris MentenKamal KishoregkbMark J. Bobak, NITSDenny KUser_UKB33hermanmolnainostreboruser12221703user6782495Arup Nanda_2d81500d9-3498-47f8-892c-b77b83c6c7edstefan jordanov1483588DavidPykelenzinJohn StegemanTom B.919723c5-1fab-4510-a20e-08cbda8b599e2738712user12138705jflackuser8277478jremacle26726e9a-ad81-4159-8596-dd053e07caebdavemmjemd01Jagdeep SangwanChristian Erlingerbinsoftuser41126721098339-W.FlavioFreitasMarwimzOuSalavat Bogdanov2687753Learco Brizzi3066685tanmoydcuser4350379MartinKlierDBARobert Podtmiguellima31771091061562Vysakh Suresh - 3035408AllenAbrahamRostyslav BurmaUser797676-OracleRamon Caballero EscamillaPhGUser_2PLWCSaibabu_Devabhaktuni-KE-2972256Jon TheriaultJanuary Tabaka-Oracle2678845Oyvind Isene1782296ananthkrk28794512617948Oleh Tyshchenkoparthibannuser5252257Phil Clifforda59290d0-b3e2-4476-812b-dd9c5c4d2b91user11993906user7328662GinoThysuser108897213241488Konstantin MylnikovtheHOrsepettejaJonathanW960326WvdHoornsensoftVivek L3259943Mahir M. QuluzadeCloudDBuser6353874VJMAparna Dutta-OraclemUday-Oracleuser943139HalldorStefan Koehlerjnicholas330Robin BuitenhuisMatheus Boesingtkiernanuser54425172603832dtspisakGbenga AjakayeLudovicoCaldaraNoname123nohupUser_67QPLStewartStevensToddBarryuser12048529GKrisuser7923359haarseMikeLBrownDejan T.Daniel E.Donald BUser_1TGCVHoekMTouny741405br0zikMathias Magnussonuser7831508User_X0Q9FChris HolliesUser_EV7JQOren NakdimonVadim Tropashko-OracleUser_N9EK3RavindraChDani Schniderazathoth11dherzhauUser_C20E8gdsotirovThomas Ranchonuser13423947Lasse JenssenahgonzalezRichard Harrison .1037293Sri Annamuser3671046David KyanekDavevaeruser11974664Kevin van DijkManik3006369user120479851244776user12167459Lukas EderThomas Teske-Oracle3224108user3609240alataiJohn_KBhavesh Tinna-OracleulohmannDavid Krch-Oracle32222673461955Dick DralMorten EganRobert MarzChrisDeBergoragaTRONd70jczuprynskiK Cannell, TH Tech3397048KevinAngusAnnelizeF49fd541f-5cea-4617-86df-2e2e3261cf43a_rintoul3136811Mark CasazzamathguyKofi1056364Peter HraškoAndy WeissJustin Warwick3485149Sam_PBEDEAnderson GonçaloLaurypnoskojohnnie.billingsAnthony EvansEmad Al-Mousauser11197070Mustafa_KALAYCIblamashachou1434Sebastien A.Danilo PiazzalungaEricKR-OracleYunus-OraclePeter GjelstrupNextNameblessed DBA3837b9ff-0b94-4e3f-9c42-ef7a10f59be83583506Goran Huser-brandon_1943619user-steve.moore_3709124d9740e4a-26f9-4aba-8ce9-4d073c2965b4Shimpei YagyuEvandro Lima-OracleEdgar ChupitRyougaMahmoud_Rabie856392Tobias B.Jairo SuarezfmzRaymond APiotr WrzosekUser_AY8YYuser10273200aakifuser3840601H_PaulineNibaluser4377404Alex Lamar-OracleGünterUser_RA3A5Oliver ABrunoVromanuser5878130lars.johan[at]ulveseth.compchircopMWRA EnQualBoneistSamuel NitscheMark R960796Erwin VrielinkJedS21122KGelling2d283f715-4e02-49fa-ab0c-f01159fba58369af3d9c-ec05-4210-a884-935987f2fb55Lunaboltuser9376047Dave SchleisLoïc Lefèvre-OracleUser_CNT03philippe_eSentinelUser_2KT88unficypuser11987530User_70H3Cpmdbarober584812User_9GEWMuser10285BartCernJazzhopTony AndrewsPeterGUser_930JVWolfgang HelmbergersdstuberJeff Kemp-OracleUser_KF4BQUser_7USOQuser462504puchtecLothar FlatzMiroslawsbird-Oracleuser12026701666645Micke VrolijkUser_9KWJBA. OlsenSeánMacGCJ. Fuda-OraclefridooNiels HeckerUser_R1YILUser_0L1L2User_DU2XKOscar de la Torre-OracleDallas DeedsjkstillPDVBVL. FernigriniRumpiSean.Scottr_h_smith2User_ZKDUAKees Oskam-OracleUser_SYXICD.Vegauser513485User_I1DOQMarcel HoefsUser_WA1TMEricaHarrisAlbert Nelson ARandolf GeistUser_MY1BMJ.SchnackenbergBlaisUser_E2O4SRichard SouleUser798041-OracleUser_9XE6Wandre.psantosUser_77701Knut HärtelUser_3HPXPUser_I5DFIUser_1JH88Thorsten Kettner
678 votes

Under Review - Voting Still Open · Last Updated

«13456728

Comments

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

    +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: 100 Employee
    edited May 18, 2016 10:50AM

    +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: 1,000 Bronze Trophy

    +1 ain't enough - +42 at least

    JW_Veldhuis-OracleApexBineDuke Ganotetheoa
  • 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: 59 Blue 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: 100 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: 59 Blue 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 Bronze Badge

    +1000 make it so, please