Forum Stats

  • 3,752,067 Users
  • 2,250,454 Discussions
  • 7,867,707 Comments

Discussions

SQL Assertions / Declarative multi-row constraints

Toon_Koppelaars-Oracle
Toon_Koppelaars-Oracle Member Posts: 98 Employee
edited Nov 20, 2017 9:31AM 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 JamadagniantonmUser_2O58P1483608andrevwinsspudgeAdricB.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_noshahibrianray30641633015851User_8BBSM050908d5-9acf-4b39-a4dd-98fb2a7868dc29ff6c77-67e6-403b-a7a4-f2437b860aacMurray SobolAndreyNuser12058618user11093747user10714229user1885282Kris JohnstonJuliusZRuslan D.user1561316Porus Homi Havewala, OCMRobbert Serne1025411Thomas DonathAdrian D17331751254358user10218349Karthick2003user9183611EricaHarrise44ab2fb-4467-4456-b068-5f3b78db6f7dDerek-LdoberkofleranjulsVic010Pranay DeepUser_8YS7Luser13469560Erik Trip - Darwin IT2967680user12026701Steve CheslerPablo Escudero3245842user566379ManishHathiAamir8143183123Shannon Severancechristoph.leserSean ZechmanBruno Carvalhouser4531167qaiserdbaOnTapypapa324636434467711-254a-44d8-8562-94db7760014fUser_MPSJT19473811926671Oscar 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 - 3035408AllenAbrahamRostyslav BurmaUser797676-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 Magnussonuser7831508User_X0Q9FChris 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 MarzChrisDeBergoragaTRONd70jczuprynskiK 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@famoususer-steve.moore_3709124Knut 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 AndrewsBlaisuser11970842User_930JVuser11222588
675 votes

Active · Last Updated

«13456728

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.

    ApexBineSebastien A.
  • Toon_Koppelaars-Oracle
    Toon_Koppelaars-Oracle Member Posts: 98 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: 999 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: 58 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: 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: 58 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 Red Ribbon

    +1000 make it so, please