Wednesday, June 29, 2005

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?

9 comments:

Tim... said...

I love those suckers.

Cascade deletes without having to worry about the cascade order, makes life worht living!

Tim... said...
This comment has been removed by a blog administrator.
Doug said...

I've found them pretty useful too, but what's really weird is I've just added a blog entry today on why Chris Date (amongst others) thinks constraint checking should *always* be immediate ...

http://doug.burns.tripod.com/oracle/index.blog?entry_id=1149661

Tim... said...

I don't get it. Perhaps I'm missing something here.

OK, theoretically you could choose to call an autonomous transaction part way through a deferred operation, but I hardly think that's likely.

All that "T1 references T2" stuff is fine, but in reality T1 and T2 see a read consistent view of the database so they don't even know the deferred operation is happening until it's complete, at which point everything is Kool and the gang!

If I'm missing the point I'd like to know more.

(Note to self. Stop mentioning autonomous transactions. You never know when TK might be listening.)

Doug said...

Tim said "If I'm missing the point I'd like to know more."

I don't think you're missing anything Tim and that was why I struggled with Date's insistence on this stuff. I think the best bit is to look at http://dbdebunk.com or Chris' book and decide for yourself - there's lots more to read on this subject. That's why I wanted to put it out there, for people to think about and have a view on.

The way I reconcile it to myself is that if we see the database as always being a consistent version of the truth which moves forward at each assigmnent, then even *my own* transaction could never see a child without a parent for example. Now, of course, you would think that if I inserted a child without a parent, knowingly, using deferred constraints, then what's the problem? But it's true that I am currently looking at an inconsistent database. If we had a multiple assignment operator, that could never be true.

But then how big might those multiple assignment statements be!

Personally I think the logic is undeniable, but that you might say - 'So what? That's never going to be a problem really ...'

Tim... said...

OK.

I take the point that without deferrable constraints the transaction can never be internally inconsistent, but if someone makes the choice to use deferrable constraints and make a mistake like that then they are a bit of a donkey. :)

Cheers

Tim...

Doug said...

Exactly what I meant by -

"... other than to highlight that problems one and two are problems if you choose to do something stupid, like writing out values half-way through a transaction that another source could use, or introducing an inconsistency in your own transaction and not being aware of it."

In fairness, there are actually five reasons he gives for immediate checking and he fleshes out the detail - this was just a taster ...

Jeff Hunter said...

1) "While it might be true, thanks to the isolation property, that no more than one transaction ever sees any particular inconsistency, the fact remains that that particular transaction does see the inconsistency and can therefore produce wrong answers"
I can kind of agree with this in theory. You change a pk value in the master and query a child table using a join, you could possibly get bad results. It would definitely be something the programmer would have to be aware of.

I don't buy 2 or 3 because of oracle's consistency.

Doug said...

Hi Jeff,

I've tried to produce an example of :-

"3) "We surely don't want every program (or other "code unit") to have to cater for the possibility that the database might be inconsistent when it's invoked."

here :-

http://doug.burns.tripod.com/oracle/index.blog?entry_id=1170846

It's a bit contrived, but it was more the overall principle I was trying to highlight.

Cheers