Mohamed Houri’s Oracle Notes

July 30, 2014

On table constraint execution order

Filed under: Oracle — hourim @ 8:03 pm

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:

  1. Before (insert) trigger fires first
  2. Then CHECK or UNIQUE constraint follows. We will see herein after in what order
  3. Then it will be the turn of after (insert) trigger
  4.  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
About these ads

2 Comments »

  1. Mohamed,

    nice test case with interesting observations. But I don’t completely agree with “They will definitely not redo the same work again” – I tend to construct the same tests again and again and again… – sometimes Goggle helps me to find what I have written some years ago; and sometimes not…

    Regards

    Martin

    Comment by Martin Preiss — July 31, 2014 @ 7:34 am | Reply

  2. Martin,

    I agree.

    I, frankly speaking, was not completely happy with this ”will definitely not” phrase. Remember Tom Kyte Mantra ‘never say never, never’. At least with each Oracle release we need to check again if things have changed or remained as is.

    Best regards

    Comment by hourim — July 31, 2014 @ 7:56 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 100 other followers

%d bloggers like this: