Violate a foreign key : FOREIGN KEY « Table « Oracle PL/SQL Tutorial

SQL>
SQL> create table p ( pk  int primary key )
  2  /

Table created.

SQL> create table c
  2  ( fk  constraint c_fk
  3        references p(pk)
  4        deferrable
  5        initially immediate
  6  )
  7  /

Table created.

SQL> insert into p values ( 1 );

1 row created.

SQL> insert into c values ( 1 );

1 row created.

SQL>
SQL> update p set pk = 2;
update p set pk = 2
*
ERROR at line 1:
ORA-02292: integrity constraint (JAVA2S.C_FK) violated - child record found


SQL>
SQL> set constraint c_fk deferred;

Constraint set.

SQL>
SQL> update p set pk = 2;

1 row updated.

SQL>
SQL> set constraint c_fk immediate;
set constraint c_fk immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (JAVA2S.C_FK) violated - parent key not found


SQL>
SQL> update c set fk = 2;

1 row updated.

SQL>
SQL> set constraint c_fk immediate;

Constraint set.

SQL>
SQL> drop table p;
drop table p
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


SQL> drop table c;

Table dropped.

SQL>
SQL>

6.16.FOREIGN KEY
	6.16.1.	Adding a FOREIGN KEY Constraint
	6.16.2.	ON DELETE CASCADE clause with a FOREIGN KEY constraint
	6.16.3.	Use the ON DELETE SET NULL clause with a FOREIGN KEY constraint
	6.16.4.	ORA-02298: cannot validate (JAVA2S.PRODUCT_ORDER_FK_PRODUCT) - parent keys not found
	6.16.5.	A foreign key to reference itself
	6.16.6.	ORA-02270: no matching unique or primary key for this column-list
	6.16.7.	Add constraint foreign key references
	6.16.8.	Violate a foreign key
	6.16.9.	Disable foreign key
	6.16.10.	Many to many using a primary-key and foreign-key relationship