Valiate foreign key example : Foreign Key « Constraints « Oracle PL / SQL

1. Oracle PL / SQL
2. Constraints
3. Foreign Key

Valiate foreign key example

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>
           
       

Related examples in the same category