#DB2 #performance #optimization #sql #sql/pl

Experiences with DB2
DB2, SQL tuning, database optimization and occasionally other database related information that interests me

Recent comments

  • May 11, 2012 8:36 am

    Oracle : Foreign key and validation

    Illustrating how the foreign key could be disabled and using the NOVALIDATE - the rows in the child table could be preserved even after enabling the foreign key. The newer rows will be checked for presence in the ‘parent’ table.

    SQL> create table test1(a number(8,2) primary key)
      2  /
                                                                               
    Table created.

    SQL> create table test2(a number(8,2) primary key, constraint fkey1 foreign key(a) references test1(a))
      2  /

    Table created.

    SQL> insert into test1 values(1)
      2  /

    1 row created.

    SQL> c/1/2
      1* insert into test2 values(1)
    SQL> /

    1 row created.


    SQL>insert into test1 values(2)
    SQL> /

    1 row created.

    SQL> c/(2)/(3)
      1* insert into test1 values(3)
    SQL> /

    1 row created.

    SQL> c/1/2
      1* insert into test2 values(3)
    SQL> /

    1 row created.

    SQL> alter table test2 disable constraint fkey1
    SQL> /

    Table altered.

    SQL> truncate table test1 
      2  /

    Table truncated.

    SQL> alter table test2 enable novalidate constraint fkey1
    SQL> /

    Table altered.

    SQL> insert into test2 values(2)
      2  /
    insert into test2 values(2)
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (SYS.FKEY1) violated - parent key not found


Apture