Deferrable Constraints

One of my developers asked me how to drop a constraint in PL/SQL today. "Don't do that" was my immediate answer.

Well, seems they have a legitimate case where they need to change a PK value in a controlled environment. I suggested deferrable constraints would give them some flexibility yet still maintain data integrity at the transaction level. I quickly got in over my head when peppered with questions about how it works and when errors are raised. So I cobbled up an example:

Lets create a master-detail relationship:

SQL> @c:\temp\create_tables.sql
SQL> drop table xyz_master cascade constraints;

Table dropped.

SQL> create table xyz_master (
 2     id          number(10) primary key,
 3     hire_date   date,
 4     common_name varchar2(200))
 5  /

Table created.

SQL> drop table xyz_detail cascade constraints
 2  /

Table dropped.

SQL> create table xyz_detail (
 2     id          number(10),
 3     pay_date    date,
 4     pay_amount  number(20,3),
 5     constraint xyz_detail_pk
 6        primary key (id, pay_date))
 7  /

Table created.

Now, lets put the deferrable fk on...

SQL> @c:\temp\add_constraints
SQL>
SQL> alter table xyz_detail
 2     add constraint xyz_master_fk1
 3        foreign key (id)
 4        references xyz_master(id)
 5     initially deferred deferrable
 6  /

Table altered.

Lets test to make sure the RI still works...

SQL> @c:\temp\add_bad1.sql
SQL> insert into xyz_detail values (1, trunc(sysdate), 100);

1 row created.

SQL> insert into xyz_detail values (1, trunc(sysdate+7), 100);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (JEFFH.XYZ_MASTER_FK1) violated - parent key
not found

Ah, I get it. The rows were added, but when I committed, the RI was checked at that point. Lets make sure the tables are clean...

SQL> select * from xyz_detail;

no rows selected

SQL> select * from xyz_master;

no rows selected

OK, that's what I expected. Lets put in some "real" data in the correct order.

SQL> @c:\temp\add_good1.sql
SQL> insert into xyz_master values (1, trunc(sysdate-365*5), 'jeff hunter');

1 row created.

SQL> insert into xyz_master values (2, trunc(sysdate-365*3), 'fred flintstone');

1 row created.

SQL> insert into xyz_master values (3, trunc(sysdate-(365*12.2)), 'mr. slate');

1 row created.

SQL> insert into xyz_detail values (1, trunc(sysdate), 100);

1 row created.

SQL> insert into xyz_detail values (1, trunc(sysdate-7), 100);

1 row created.

SQL> insert into xyz_detail values (1, trunc(sysdate-14), 100);

1 row created.

SQL> insert into xyz_detail values (1, trunc(sysdate-21), 100);
1 row created.

SQL> insert into xyz_detail values (2, trunc(sysdate), 22.22);

1 row created.

SQL> insert into xyz_detail values (2, trunc(sysdate-7), 22.22);

1 row created.

SQL> insert into xyz_detail values (2, trunc(sysdate-14), 22.22);

1 row created.

SQL> insert into xyz_detail values (2, trunc(sysdate-21), 22.22);

1 row created.

SQL> insert into xyz_detail values (3, trunc(sysdate), 1000);

1 row created.

SQL> insert into xyz_detail values (3, trunc(sysdate-7), 1000);

1 row created.

SQL> insert into xyz_detail values (3, trunc(sysdate-14), 1000);

1 row created.

SQL> insert into xyz_detail values (3, trunc(sysdate-21), 1000);

1 row created.

SQL> commit;

Commit complete.

I expected that to work. Lets try to insert the details and then the master record just for kicks.

SQL> @c:\temp\add_wrong_order1.sql
SQL>
SQL> insert into xyz_detail values (4, trunc(sysdate), 100);

1 row created.

SQL> insert into xyz_detail values (4, trunc(sysdate-7), 100);

1 row created.

SQL> insert into xyz_detail values (4, trunc(sysdate-14), 100);

1 row created.

SQL> insert into xyz_detail values (4, trunc(sysdate-21), 100);

1 row created.

SQL> insert into xyz_master values (4, trunc(sysdate-(365*12.2)), 'barney rubble');

1 row created.

SQL> commit;

Commit complete.

That's cool, that works. Lets try to violate the PK to see what happens.

SQL> @c:\temp\add_bad2.sql
SQL> insert into xyz_detail values (1, trunc(sysdate), 100);
insert into xyz_detail values (1, trunc(sysdate), 100)
*
ERROR at line 1:
ORA-00001: unique constraint (JEFFH.XYZ_DETAIL_PK) violated


SQL> insert into xyz_detail values (1, trunc(sysdate-7), 100);
insert into xyz_detail values (1, trunc(sysdate-7), 100)
*
ERROR at line 1:
ORA-00001: unique constraint (JEFFH.XYZ_DETAIL_PK) violated


SQL> insert into xyz_detail values (1, trunc(sysdate-14), 100);
insert into xyz_detail values (1, trunc(sysdate-14), 100)
*
ERROR at line 1:
ORA-00001: unique constraint (JEFFH.XYZ_DETAIL_PK) violated


SQL> commit;

Commit complete.

Right. The PK is not deferrable. Lets change the PK value in the master and then change the PK value in the detail records.

SQL> @c:\temp\update1.sql
SQL> update xyz_master set id=10 where id=1;

1 row updated.

SQL> update xyz_detail set id=10 where id=1;

4 rows updated.

SQL> commit;

Commit complete.

That works, my developer will be happy. Just for kicks, lets try it the other way around.

SQL> @c:\temp\update2.sql
SQL> update xyz_detail set id=20 where id=2;

4 rows updated.

SQL> update xyz_master set id=20 where id=2;

1 row updated.

SQL> commit;

Commit complete.

Of course that works. Let's just double check to make sure RI stays in tact (like we have any doubts now)...

SQL> @c:\temp\update3.sql
SQL> update xyz_master set id=30 where id=3;

1 row updated.

SQL> update xyz_detail set id=30 where id=4;

4 rows updated.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (JEFFH.XYZ_MASTER_FK1) violated - child record
found

Sure enough. I still need to investigate any locking issues, but it looks like this might be what we are looking for in this particular case. If anybody has any hints on what to look out for, I'd appreciate it. Also, assuming we have a legitimate business case, do you think this is an appropriate use of deferrable constraints?