9 Replies Latest reply on Jul 30, 2014 12:31 PM by Mohamed Houri

    Constraints on table

    961315


      Hi All,

       

      Assume that i have below table structure

      create table emp

      (

      emp_id number primary key,

      dept_id number references dept(dept_id),

      sal number check (sal >5000)

      );

       

      If a try below insert:

      insert into emp values (1,30,1000);

      This query is throwing constraint violation for check constraint not foreign key constraint (assume i dont have dept_id=30 in dept table). Is there any sequence in which constraints are checked in oracle.

        • 1. Re: Constraints on table
          HuaMin Chen

          It will check one by one. For this case, it will first check the existence of the FK and then check the salary constraint.

          • 2. Re: Constraints on table
            961315

            Oracle is checking column by column for primary key, not null and check constraint only. It is checking foreign key at the end even if dept_id column is coming before sal column.

            • 3. Re: Constraints on table
              Paul  Horth

              961315 wrote:

               

              Oracle is checking column by column for primary key, not null and check constraint only. It is checking foreign key at the end even if dept_id column is coming before sal column.

              No, columns don't have an order in a relational DB table, just like rows don't have an order.

               

              So, you can't really say dept_id comes before sal.

               

              I therefore think that check constraints are checked in an arbitrary order. I have to say I haven't seen any documentation to back this opinion up though.

              • 4. Re: Constraints on table
                Frank Kulash

                Hi,

                 

                I'm just curious: Why does it matter, in your application, which error gets raised?

                • 5. Re: Constraints on table
                  961315

                  It is not impacting my application in way. Just wanted to know if there is any reason for the above mantioned bahaviour of the constraints.

                  • 6. Re: Constraints on table
                    1909

                    Could you please provide the exact violation message  thrown?

                    • 7. Re: Constraints on table
                      Chanchal Wankhade

                      Hi,

                       

                      Just an assumption:-  Its seems oracle check all the constraint on the primary table first then go to the reference table. (This is no where documented)

                       

                       

                      Regards,
                      Chanchal Wankhade.

                      • 8. Re: Re: Constraints on table
                        Karthick2003

                        Paul Horth wrote:

                         

                        I therefore think that check constraints are checked in an arbitrary order. I have to say I haven't seen any documentation to back this opinion up though.

                         

                        As you said, I could not find any document link to support this. But I think COLUMN_ID in ALL/USER/DBA_TAB_COLUMNS determines the order of execution of columns within a specific constraint.

                         

                        SQL> create table a
                          2  (
                          3     col1 integer not null,
                          4     col2 integer not null,
                          5     col3 integer not null
                          6  );
                        
                        Table created.
                        
                        SQL> create table b
                          2  (
                          3     col3 integer not null,
                          4     col2 integer not null,
                          5     col1 integer not null
                          6  );
                        
                        Table created.
                        
                        SQL> select table_name, column_name, column_id
                          2    from user_tab_columns
                          3   where table_name in ('A','B')
                          4   order
                          5      by 1, 3;
                        
                        TABLE_NAME                     COLUMN_NAME                     COLUMN_ID
                        ------------------------------ ------------------------------ ----------
                        A                              COL1                                    1
                        A                              COL2                                    2
                        A                              COL3                                    3
                        B                              COL3                                    1
                        B                              COL2                                    2
                        B                              COL1                                    3
                        
                        6 rows selected.
                        
                        SQL> insert into a(col1, col2, col3) values (null, null, null);
                        insert into a(col1, col2, col3) values (null, null, null)
                                                                *
                        ERROR at line 1:
                        ORA-01400: cannot insert NULL into ("KARTHICK"."A"."COL1")
                        
                        SQL> insert into b(col1, col2, col3) values (null, null, null);
                        insert into b(col1, col2, col3) values (null, null, null)
                                                                            *
                        ERROR at line 1:
                        ORA-01400: cannot insert NULL into ("KARTHICK"."B"."COL3")
                        
                        SQL>
                        

                         

                         

                         

                         

                         

                         

                         


                        1 person found this helpful
                        • 9. Re: Re: Re: Constraints on table
                          Mohamed Houri

                          Karthick_Arp a écrit:

                          As you said, I could not find any document link to support this. But I think COLUMN_ID in ALL/USER/DBA_TAB_COLUMNS determines the order of execution of columns within a specific constraint.

                            

                           

                          I would have said "columns within a specific CHECK or UNIQUE constraint". Because Foreign Key constraints seems to be verified at the end of the insert/update process and

                          1. after check and unique constraints in the column table order
                          2. after  before and after trigger

                           

                          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;

                          /       

                                 

                          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

                           

                          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")

                           

                           

                          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

                           

                           

                          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

                           

                           

                          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

                           

                          Best regards

                          Mohamed Houri

                          1 person found this helpful