SQL> create table parent( pk int,
2 constraint parent_pk primary key(pk) );
Table created.
SQL>
SQL> create table child ( fk,
2 constraint child_fk foreign key(fk)
3 references parent deferrable );
Table created.
SQL>
SQL> insert into parent values( 1 );
1 row created.
SQL> insert into child values( 1 );
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> update parent set pk = 2;
update parent set pk = 2
*
ERROR at line 1:
ORA-02292: integrity constraint (JAVA2S.CHILD_FK) violated - child record found
SQL>
SQL> update child set fk = 2;
update child set fk = 2
*
ERROR at line 1:
ORA-02291: integrity constraint (JAVA2S.CHILD_FK) violated - parent key not found
SQL>
SQL> set constraints child_fk deferred;
Constraint set.
SQL>
SQL> update parent set pk=2;
1 row updated.
SQL>
SQL> select * from parent;
PK
----------
2
SQL> select * from child;
FK
----------
1
SQL>
SQL>
SQL> set constraints child_fk deferred;
Constraint set.
SQL> update parent set pk=2;
1 row updated.
SQL>
SQL> select * from parent;
PK
----------
2
SQL> select * from child;
FK
----------
1
SQL>
SQL> set constraints child_fk immediate;
set constraints child_fk immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (JAVA2S.CHILD_FK) violated - parent key not found
SQL> update child set fk = 2;
1 row updated.
SQL> set constraints child_fk immediate;
Constraint set.
SQL>
SQL> select * from parent;
PK
----------
2
SQL> select * from child;
FK
----------
2
SQL>
SQL> drop table parent cascade constraints;
Table dropped.
SQL>
SQL> drop table child cascade constraints;
Table dropped.
SQL>
SQL>
SQL>
|