Introduction
Before to be left behind due to my infrequent blogging activity these days, here it is a simple note about the order of execution of constraints and triggers placed on a given table. Basically, I am inclined, according to my very recent tests done on 11.2.0.3 and shown below, to claimn that that order of execution obeys the following steps:
- Before (insert) trigger fires first
- Then CHECK or UNIQUE constraint follows. We will see herein after in what order
- Then it will be the turn of after (insert) trigger
- And finally checking Foreign Key integrity constraint comes at the trailing edge of the process
The execution order of the check and unique constraints seems to be closely related to the order of the columns (in the table) with which the constraints has been implemented, as shown by Karthick_App in the above mentioned otn thread.
Details
This is the model on which I have made the above observations:
drop table c purge;
drop table p purge;
create table p as select rownum n1, trunc((rownum-1)/3) n2
from dual connect by level <= 10;
alter table p add constraint p_pk primary key (n1);
create table c (m1 number not null, m2 number, m3 number);
alter table c add constraint child_fk foreign key (m2) references p(n1);
alter table c add constraint child_ck check (m3 in (1,2));
alter table c add constraint child_uk unique (m1);
create or replace trigger c_bfi_trg before insert on c
for each row
begin
dbms_output.put_line('Trigger before insert fired');
end;
/
create or replace trigger c_afi_trg after insert on c
for each row
begin
dbms_output.put_line('Trigger after insert fired');
end;
/
The following select shows the order of the column within the table
select table_name, column_name, column_id from user_tab_columns where table_name = 'C' order by 1, 3; TABLE_NAME COLUMN_NAME COLUMN_ID ------------------------------ ------------------------------ ---------- C M1 1 C M2 2 C M3 3
Note particularly that I have:
- A not null CHECK constraint on the first column (m1) because it has been declared as the primary key of the child table c
- A UNIQUE constraint on the first column (m1) because it has been declared as the primary key of the child table c
- A FOREIGN Key constraint on the second column (m2) because it has been declared as a foreign key to the parent table p
- A CHECK constraint on the third column(m3) to allow only 1 and 2 as possible values
A check constraint (being it unique or check on possible values) other than a foreign key constraint is executed according to the column order in the table. For example, the not null CHECK constraint will be the first to be verified because it has been implemented on column m1 which is the leading column of the table
SQL> set serveroutput on
SQL> insert into c values (null, 11, 3);
Trigger before insert fired
insert into c values (null, 11, 3)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("XXX"."C"."M1")
Note that in the above insert, I have 3 reasons for which the insert ought to be refused
- Inserting a null value into the primary key (m1)
- Inserting a non-existing parent key (m2 = 11)
- Inserting a non-acceptable value into m3 (m3 in (1,2))
Among all those refusal reasons, it is the ORA-01400 that has fired up because the NOT NULL constraint has been implemented on the first column of the table (m1). The same observation can be made when the unique constraint placed on the first column is violated as shown below:
SQL> insert into c values (1, 10,1); Trigger before insert fired Trigger after insert fired 1 row created. SQL> insert into c values (1, 11,3); Trigger before insert fired insert into c values (1, 11,3) * ERROR at line 1: ORA-00001: unique constraint (XXX.CHILD_UK) violated
When we work around the not null refusal reason by supplying a not null value to the m1 column and re-insert the same statement, this time it is the check constraint m3 in (1,2) that raises up
SQL> insert into c values (1, 11,3); Trigger before insert fired insert into c values (1, 11,3) * ERROR at line 1: ORA-02290: check constraint (XXX.CHILD_CK) violated
Despite the Foreign key is placed on the second column of the table, it is the check constraint on the third column that fires up.
And when all constraints are valid, the foreign key integrity constraint is finally checked as shown below:
SQL> insert into c values (1, 11,1); Trigger before insert fired Trigger after insert fired insert into c values (1, 11,1) * ERROR at line 1: ORA-02291: integrity constraint (XXX.CHILD_FK) violated - parent key not found
Conclusion
I have been very often asking myself (or being asked) the same question when confronted to such a kind of situation: what is the order of the constraint/trigger execution? And for each time I was obliged to spend few minutes creating a simple model in order to come up with a reliable answer. From now and on, I am sure that I will not redo this work as far as I definitely know that I wrote the answer into an article that has been published somewhere on the net.
In passing, spot why I am encouraging people to write:
- They will definitely not redo the same work again
- They will definitely find easily what they have done in this context
- They will definitely let other readers validate or correct their answer
Mohamed Houri’s Oracle Notes 