{"id":968,"date":"2005-07-22T12:00:00","date_gmt":"2005-07-22T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=968"},"modified":"2005-07-22T12:00:00","modified_gmt":"2005-07-22T12:00:00","slug":"deferred-constraint-checking","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2005\/07\/22\/deferred-constraint-checking\/","title":{"rendered":"Deferred Constraint Checking"},"content":{"rendered":"

In my previous blog entry I highlighted a few of the reasons that integrity constraint checking should not be deferred, stated by Date in his recent book (and by him and others in other papers). At the time and while looking at an entry over on Jeff Hunter’s Blog<\/a> I felt that functions might be a particular problem. My mind also went back to a chat with Carl Dudley and others after a Scottish SIG meeting when Carl had mumbled something about functions that had stuck somewhere in the back of my mind.<\/p>\n

So I thought I\ufffdd put together a simple example based on EMP and DEPT to illustrate some of the problems you could run into with deferred constraint checking. Here\ufffds the data (unmodified from a standard SCOTT installation on 10.1.0.4)<\/p>\n

SQL> SELECT empno, deptno, ename, sal 2 FROM emp 3 ORDER BY deptno, empno; 
<\/p>EMPNO      DEPTNO     ENAME      SAL---------- ---------- ---------- ---------- 7782               10 CLARK            2450 7839               10 KING             50007934               10 MILLER           13007369               20 SMITH             8007566               20 JONES            29757788               20 SCOTT            30007876               20 ADAMS            1100 7902               20 FORD             3000 7499               30 ALLEN            1600 7521               30 WARD             1250 7654               30 MARTIN           1250 7698               30 BLAKE            2850 7844               30 TURNER           1500 7900               30 JAMES             950
<\/p>14 rows selected
<\/p>SQL> SELECT deptno, dname 2 from dept; 
<\/p>
<\/p>DEPTNO     DNAME---------- -------------- 10         ACCOUNTING 20         RESEARCH 30         SALES 40         OPERATIONS<\/pre>\n
So next I\ufffdll create a simple function to test. The function just calculates the average salary for employees in the department specified by the p_dname parameter<\/p>\n
SQL> CREATE OR REPLACE FUNCTION func_avg_salary (p_dname dept.dname%TYPE) 2 RETURN NUMBER 3 IS 4 n_avg_salary NUMBER; 5 BEGIN 6 SELECT AVG(emp.sal) INTO n_avg_salary 7 FROM emp, dept 8 WHERE emp.DEPTNO = dept.DEPTNO 9 AND dept.dname = p_dname; 10 11 RETURN n_avg_salary; 12 END; 13 \/
<\/p>Function created.<\/pre>\n
Note that anything this function does during this test could easily be more efficiently replaced by single SQL statements (avoiding PL\/SQL altogether) but I mean to illustrate the problem at a simple level. I hope you can use your imagination to dream up much more worrying examples, involving more complex functions against a more complex model. It\ufffds also worth thinking about whether a function might communicate outside of this transaction, for example by sending results out to file or via an email?<\/p>\n
Now I want to make sure that I have a deferred constraint to work with<\/p>\n
SQL> column table_name format a16SQL> column constraint_name format a16SQL> column column_name format a16SQL> SELECT c.table_name, c.constraint_name, c.constraint_type, 2 col.column_name, col.position 3 FROM user_constraints c, user_cons_columns col 4 WHERE c.constraint_name = col.constraint_name
<\/p>SQL> \/TABLE_NAME       CONSTRAINT_NAME  C COLUMN_NAME      POSITION---------------- ---------------- - ---------------- ----------DEPT             PK_DEPT          P DEPTNO                    1EMP              PK_EMP           P EMPNO                     1EMP              FK_DEPTNO        R DEPTNO                    1
<\/p>SQL> ALTER TABLE emp DROP CONSTRAINT fk_deptno;
<\/p>Table altered.
<\/p>SQL> ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept 2 INITIALLY IMMEDIATE DEFERRABLE;
<\/p>Table altered.<\/pre>\n
Now that I have the deferrable foreign key constraint, I\ufffdm going to defer the checking until the end of the transaction.<\/p>\n
SQL> SET CONSTRAINT FK_DEPTNO DEFERRED;
<\/p>Constraint set.<\/pre>\n
That will let me insert some employees who work for the non-existent department 90.<\/p>\n
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) 2 values (8000, 'BURNS', 'DBA', 7902, SYSDATE, 9000, 0, 90);
<\/p>1 row created.
<\/p>SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) 2 values (8001, 'JONES', 'DBA', 7902, SYSDATE, 5000, 0, 90);
<\/p>1 row created.<\/pre>\n
So now I *know* that I have inconsistent data. Or do I? (as James Burke<\/a> may have said). In this case I do because I\ufffdm working with a very small number of statements; I checked which departments existed before I ran them; and I\ufffdve just deferred the checking of the foreign key constraint. If I don\ufffdt know what I\ufffdve just done, that\ufffds what I would probably classify as \ufffddoing something stupid\ufffd. However, let\ufffds use the function anyway.<\/p>\n
SQL> COLUMN avg_sal FORMAT 999,990.90SQL> select dname, loc, ROUND(func_avg_salary(dname), 2) avg_sal 2 from dept;
<\/p>DNAME          LOC               AVG_SAL-------------- ------------- -----------ACCOUNTING     NEW YORK         2,916.67RESEARCH       DALLAS           2,175.00SALES          CHICAGO          1,566.67OPERATIONS     BOSTON<\/pre>\n
The results are correct really. For all of the departments that exist in the DEPT table, the function displays the average employee salary for that department. Of course, the two new employees that I just inserted are discounted because their department is non-existent. Whether this matters or not depends on the results you were expecting!<\/p>\n
Again, this is all pretty obvious, but I think the important thing here is that whoever wrote the function originally (some time in the distant past) was almost definitely expecting every employee to work for an existing department. That\ufffds the whole point of having implemented the relational model and the constraints in the first place isn\ufffdt it? The author of the function could consider the possibility that there is invalid data in the database, but that\ufffds going to require more work from the developer when the RDBMS should be guaranteeing this. Even if the developer of every function does check for any possible data anomalies \ufffd a big job across an application that seems a little pointless to me \ufffd am I going to have to check the code of every function to make sure that it performs these checks?<\/p>\n
All of which is not to say that I think deferred constraint checking is a terribly bad thing \ufffd I\ufffdm still quite a fan for those nightmarish initial data population exercises (if you\ufffdve ever been hit by that, you\ufffdll know what I mean). However, there are dangers and I\ufffdve moved from a position of thinking that constraint checking could be deferred comfortably to thinking that it should only be used with care.<\/p>\n
For those who haven\ufffdt used deferred constraint checking before, here\ufffds what happens when I try to commit the invalid data<\/p>\n
SQL> commit;commit*ERROR at line 1:ORA-02091: transaction rolled backORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found<\/pre>\n","protected":false},"excerpt":{"rendered":"
In my previous blog entry I highlighted a few of the reasons that integrity constraint checking should not be deferred, stated by Date in his recent book (and by him and others in other papers). At the time and while looking at an entry over on Jeff Hunter’s Blog I felt that functions might be… Continue reading Deferred Constraint Checking<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-968","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":969,"url":"http:\/\/orcldoug.com\/blog\/2005\/06\/30\/the-multiple-assignment-operator-and-deferred-constraints\/","url_meta":{"origin":968,"position":0},"title":"The Multiple Assignment Operator and Deferred Constraints","date":"June 30, 2005","format":false,"excerpt":"I attended a day long seminar in Edinburgh last year with Chris Date lecturing and it was a rare treat. Thanks again to Peter Robson for organising this.Although I'm not the most academically-inclined techie, which I'm always honest about, I found what Chris had to say enlightening and mostly entertaining.\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":933,"url":"http:\/\/orcldoug.com\/blog\/2005\/10\/17\/ansi-join-syntax\/","url_meta":{"origin":968,"position":1},"title":"ANSI Join Syntax","date":"October 17, 2005","format":false,"excerpt":"A couple of days ago one of my colleagues raised the question - 'ANSI join syntax, does anyone actually use that?' I have pretty strong views on this and so a brief dynamic conversation followed (and that isn't a euphemism for 'stand-up row', I should add!) So, what about ANSI\u2026","rel":"","context":"With 20 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1555,"url":"http:\/\/orcldoug.com\/blog\/2009\/12\/18\/bug-hunting\/","url_meta":{"origin":968,"position":2},"title":"Bug Hunting","date":"December 18, 2009","format":false,"excerpt":"It's early days but I think I became the joint father of a bug report this week. This is bug number 9219636. SQL> CREATE TYPE INTEGER_ARRAY_T AS TABLE OF INTEGER \u00a0 2\u00a0 \/ Type created. SQL> SQL> CREATE TABLE V_SESSION_VALID ( \u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SID NUMBER , \u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SESSION_ID NUMBER\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":938,"url":"http:\/\/orcldoug.com\/blog\/2005\/09\/29\/html-db\/","url_meta":{"origin":968,"position":3},"title":"HTML DB","date":"September 29, 2005","format":false,"excerpt":"Today I had one of those minor buzzes of excitement when I started to play around with Oracle's hosted HTML DB environment.Without wanting to go into my current feelings about work, you could say things are a little quiet. I'm trying to find things to do but I've joined just\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1060,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/19\/template-issues\/","url_meta":{"origin":968,"position":4},"title":"Template Issues","date":"August 19, 2006","format":false,"excerpt":"THE FINAL UPDATE - 21st August 2006Template Issues? I don't think so! More like user error \ud83d\ude09 It turns out that all of my template\/RSS\/blog problems were self-inflicted because I'd stupidly renamed my templates\/default folder to something else. What I hadn't realised was that, a bit like the DEFAULT profile\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":930,"url":"http:\/\/orcldoug.com\/blog\/2005\/10\/21\/ukoug-agenda\/","url_meta":{"origin":968,"position":5},"title":"UKOUG Agenda","date":"October 21, 2005","format":false,"excerpt":"What's good enough for Niall is good enough for me ... Here's my agenda for the UKOUG conferenceSunday - Oak Table day. Like Niall, I won't be there at the start, but even half a day of this standard will add a lot to the conference for me. Then onto\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/968","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=968"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/968\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=968"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=968"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=968"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}