|
SQL>
SQL> create table parent( pk int,constraint parent_pk primary key(pk) );
Table created.
SQL> create table child ( fk,constraint child_fk foreign key(fk)
2 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> commit;
Commit complete.
SQL> update parent set pk = 2;
update parent set pk = 2
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.CHILD_FK) violated - child record found
SQL> update child set fk = 2;
update child set fk = 2
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.CHILD_FK) violated - parent key not found
SQL> set constraints child_fk deferred;
Constraint set.
SQL> update parent set pk=2;
1 row updated.
SQL> select * from parent;
PK
----------
2
SQL> select * from child;
FK
----------
1
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (SYS.CHILD_FK) violated - child record found
SQL> set constraints child_fk deferred;
Constraint set.
SQL> update parent set pk=2;
1 row updated.
SQL> select * from parent;
PK
----------
2
SQL> select * from child;
FK
----------
1
SQL> set constraints child_fk immediate;
set constraints child_fk immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.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> commit;
Commit complete.
SQL> select * from parent;
PK
----------
2
SQL> select * from child;
FK
----------
2
SQL>
SQL> drop table child cascade constraints;
Table dropped.
SQL> drop table parent cascade constraints;
Table dropped.
|