Deferred constraint real life scenario
Motto:
It is worth to know oracle features even if you have never used them.
Some days ago one of our developers came in and started to tell that our testers found another bug. This time bug is raised in Oracle by violated unique key. Of course developer didn't catch it in unit testing because it was a bit tricky but our testers were smart and nasty :) enough to dig it out.
So we had a form that may have many child records for a previously chosen parent record. To feel something concrete let's choose for the parent an order for tax discount - suppose you have submitted it in written form to your local government (not that I'm suggesting you to do that :))). But of course your local government doesn't want to give you tax discount and therefore as always asks you many documents explaining reason for it. But there is one restriction - each document must be of different kind e.g. one proving that you have 5 children, second proving that your rent has risen by 25% etc.
So as I strongly believe that business constraints should be enforced in db as much as possible I've created UK in supporting documents for two columns:
1) FK to order table and
2) document type.
Unfortunately in the form where all documents are entered one can change all attributes and our evil testers of course found the most evil one - swapped type of document A to document B and document B to document A.
So where are the problem you ask? Yea there aren't any problem if one looks on the start point (docs A and B for one order) and finish point (docs B and A for the same order), but somehow situation in the middle is very unpleasant. There is a moment when we have 2 documents with type B simultaneously. And here testers got the error as below:
SQL> create table parent (par_id number not null constraint par_pk primary key);
Table created.
SQL> ed
Wrote file afiedt.buf
1 create table child (cld_id number not null constraint cld_pk primary key,
2 cld_par_id number not null constraint cld_par_fk references parent (par_id),
3 cld_type varchar2(3) not null,
4 cld_data varchar2(100),
5* constraint cld_uk unique (cld_par_id, cld_type))
SQL> /
Table created.
SQL> insert into parent values (1);
1 row created.
SQL> insert into child values (1, 1, 'A', 'Doc type A');
1 row created.
SQL> insert into child values (2, 1, 'B', 'Doc type B');
1 row created.
SQL> commit;
Commit complete.
SQL> update child set cld_type = 'B' where cld_id = 1;
update child set cld_type = 'B' where cld_id = 1
*
ERROR at line 1:ORA-00001: unique constraint (GINTS.CLD_UK) violated
So what to do?
My immediate response was - deferred constraints! I definitely knew that I don't want to loose the constraint forever because I'd like to enforce as much as possible integrity constraints in DB. So how it looks with deferred constraints?
SQL> alter table child drop constraint cld_uk;
Table altered.
SQL> ed
Wrote file afiedt.buf
1 alter table child add constraint cld_uk unique (cld_par_id, cld_type)
2* deferrable initially deferred
SQL> /
Table altered.
SQL> update child set cld_type = 'B' where cld_id = 1;
1 row updated.
SQL> update child set cld_type = 'A' where cld_id = 2;
1 row updated.
SQL> commit;
Commit complete.
Everything works!
How does it look like now?
SQL> select cld_id, cld_par_id, cld_type from child;
CLD_ID CLD_PAR_ID CLD
---------- ---------- ---
1 1 B
2 1 A
Let's try some more experiments. Let's update only one row back and try to commit:
SQL> update child set cld_type = 'A' where cld_id = 1;
1 row updated.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (GINTS.CLD_UK) violated
Bahhh!!! We got expected error!
The only thing - one has to remember that deferred constraints rolls back ALL transaction since it started even if the changed rows are completely valid and unrelated to the deferred constraint for example let's try to insert one completely valid row in parent table at first and then try to do nasty things with deferred constraint:
SQL> insert into parent values (2);
1 row created.
SQL> update child set cld_type = 'A' where cld_id = 1;
1 row updated.
SQL> select * from parent;
PAR_ID
----------
1
2
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (GINTS.CLD_UK) violated
SQL> select * from parent;
PAR_ID
----------
1
Our inserted row into parent table also has gone. With normal (i.e. not deferred) constraint Oracle rolls back only changes made by the latest statement not all statements since transaction started. One has to remember this behaviour when using deferred constraints.
Quite popular use of deferred constraints seems to be update of primary keys (and after that also foreign keys) but as I'm always (OK almost always) using surrogates and primary key update for me is something absolutely awful I've never had such need and I'd never recommend that to anyone.
