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 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.
So I thought I�d 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�s the data (unmodified from a standard SCOTT installation on 10.1.0.4)
SQL> SELECT empno, deptno, ename, sal
2 FROM emp
3 ORDER BY deptno, empno;
EMPNO DEPTNO ENAME SAL
---------- ---------- ---------- ----------
7782 10 CLARK 2450
7839 10 KING 5000
7934 10 MILLER 1300
7369 20 SMITH 800
7566 20 JONES 2975
7788 20 SCOTT 3000
7876 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
14 rows selected
SQL> SELECT deptno, dname
2 from dept;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
So next I�ll create a simple function to test. The function just calculates the average salary for employees in the department specified by the p_dname parameter
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 /
Function created.
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�s 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?
Now I want to make sure that I have a deferred constraint to work with
SQL> column table_name format a16
SQL> column constraint_name format a16
SQL> column column_name format a16
SQL> 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
SQL> /
TABLE_NAME CONSTRAINT_NAME C COLUMN_NAME POSITION
---------------- ---------------- - ---------------- ----------
DEPT PK_DEPT P DEPTNO 1
EMP PK_EMP P EMPNO 1
EMP FK_DEPTNO R DEPTNO 1
SQL> ALTER TABLE emp DROP CONSTRAINT fk_deptno;
Table altered.
SQL> ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept
2 INITIALLY IMMEDIATE DEFERRABLE;
Table altered.
Now that I have the deferrable foreign key constraint, I�m going to defer the checking until the end of the transaction.
SQL> SET CONSTRAINT FK_DEPTNO DEFERRED;
Constraint set.
That will let me insert some employees who work for the non-existent department 90.
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
2 values (8000, 'BURNS', 'DBA', 7902, SYSDATE, 9000, 0, 90);
1 row created.
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
2 values (8001, 'JONES', 'DBA', 7902, SYSDATE, 5000, 0, 90);
1 row created.
So now I *know* that I have inconsistent data. Or do I? (as James Burke may have said). In this case I do because I�m working with a very small number of statements; I checked which departments existed before I ran them; and I�ve just deferred the checking of the foreign key constraint. If I don�t know what I�ve just done, that�s what I would probably classify as �doing something stupid�. However, let�s use the function anyway.
SQL> COLUMN avg_sal FORMAT 999,990.90
SQL> select dname, loc, ROUND(func_avg_salary(dname), 2) avg_sal
2 from dept;
DNAME LOC AVG_SAL
-------------- ------------- -----------
ACCOUNTING NEW YORK 2,916.67
RESEARCH DALLAS 2,175.00
SALES CHICAGO 1,566.67
OPERATIONS BOSTON
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!
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�s the whole point of having implemented the relational model and the constraints in the first place isn�t it? The author of the function could consider the possibility that there is invalid data in the database, but that�s 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 � a big job across an application that seems a little pointless to me � am I going to have to check the code of every function to make sure that it performs these checks?
All of which is not to say that I think deferred constraint checking is a terribly bad thing � I�m still quite a fan for those nightmarish initial data population exercises (if you�ve ever been hit by that, you�ll know what I mean). However, there are dangers and I�ve moved from a position of thinking that constraint checking could be deferred comfortably to thinking that it should only be used with care.
For those who haven�t used deferred constraint checking before, here�s what happens when I try to commit the invalid data
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found