Violate the primary key and foreign key relation : Create Primary Key « Constraints « Oracle PL / SQL

1. Oracle PL / SQL
2. Constraints
3. Create Primary Key

Violate the primary key and foreign key relation

   
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.

   
    
  

Related examples in the same category