Thursday, January 12, 2006

deferred constraints part 1

Some interesting facts about constraints:
If you must update the primary key and the foreign key
and they are located in different tables then you can get into troubles:
If yo disable the foreign key it can happen that you will not be able to enable tit anymore because somebody has messed around with the values while it was disabled. You could get a full table lock to prevent this but this might be difficult also.

Today we have had a closer look at deferrable constraints.
These can be a good alternative for disabeling constraints for a cascading UPDATE.

It is essential to create the constraint as DEFERRABLE right away when creating it, if you want to be able to deferre the constraint chaeck to the end of the transaction.
This is not modifyable afterwards.
If you do not specify DEFERRABLE INITIALLY DEFEERED as creation time but only specify DEFERRABLE ,
then DEFERRABLE INITIALLY IMMEDIATE is the default.

Let's see how they work:
PREREQUISITS:
- first I create a Primary key and a foreign key relationship.


SQL> ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (department_id);
Table altered.

SQL> ALTER TABLE emp ADD CONSTRAINT emp_fk FOREIGN KEY (department_id)
2 REFERENCES DEPT (department_id)
3 DEFERRABLE;
Table altered.

SQL> SELECT constraint_name, search_condition, constraint_type, deferrable, deferred
2 FROM user_constraints
3* WHERE table_name='EMP'

CONSTRAINT_NAME SEARCH_CONDITION C DEFERRABLE DEFERRED
------------------------------ ------------------------------ - -------------- ---------
SYS_C002768 "LAST_NAME" IS NOT NULL C NOT DEFERRABLE IMMEDIATE
SYS_C002769 "EMAIL" IS NOT NULL C NOT DEFERRABLE IMMEDIATE
SYS_C002770 "HIRE_DATE" IS NOT NULL C NOT DEFERRABLE IMMEDIATE
SYS_C002771 "JOB_ID" IS NOT NULL C NOT DEFERRABLE IMMEDIATE
EMP_FK R DEFERRABLE IMMEDIATE
EMP_PK P NOT DEFERRABLE IMMEDIATE
EMP_FK_MGR R NOT DEFERRABLE IMMEDIATE
CHECK_SAL "SAL">500 C NOT DEFERRABLE IMMEDIATE

DEMO 1:
- then I try my updates on the key and a foreign key, does not work of course
- now I set the deferrable constraints for my session to deferred
- then I try my updates on the key and a foreign key again and it works,
because the constraint-checking is deferred to the end of the transaction
- now I make a critical update which will cause a constraint volation at commit time
this will cause a TARNSACTION LEVEL ROLLBACK at commit time!!!!

DEMO 2:
- I do the same steps as in DEMO 1, only that I change the state of my deferrable constraints to immediate checking before my critical update.
=> this causes a STATEMENT LEVEL ROLLBACK right after the statement is processed,
the other two updates if issued before this are not rolled back this time!!!!!!

This is DEMO 1:

SQL> UPDATE emp SET department_id=department_id+1000;
UPDATE emp SET department_id=department_id+1000
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_FK) violated - parent key not found

SQL> UPDATE dept SET department_id=department_id+1000;
UPDATE dept SET department_id=department_id+1000
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_FK) violated - child record found


SQL> alter session set constraints=deferred;

SQL> UPDATE emp SET department_id=department_id+1000;
107 rows updated.

SQL> update dept set department_id=department_id+1000;
27 rows updated.

SQL> update dept set department_id=8888 where department_id=1090; # this is the critical update
1 row updated.

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


This is DEMO 2:

SQL> update dept set department_id=department_id+1000;

27 rows updated.

SQL> update emp set department_id=department_id+1000;

107 rows updated.

SQL> alter session set constraints=immediate;

Session altered.

SQL> update dept set department_id=8888 where department_id=1090;
update dept set department_id=8888 where department_id=1090
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_FK) violated - child record found

Some very good examples for the usage of deferred constraints you can find at asktom.oracle.com

3 Comments:

At Sat Mar 03, 08:39:00 PM, Anonymous Anonymous said...

very nice post...really helpful
thanks

santosh

 
At Sat Jul 07, 03:25:00 PM, Anonymous Anonymous said...

CAN YOU PLEASE POST SOME EXAMPLES FOR THE CONTRAINTS RELATED TO VIEWS...

 
At Sat Jul 07, 03:26:00 PM, Anonymous Anonymous said...

Can you please post few examples on using constraints for views

 

Post a Comment

<< Home