Use the ON DELETE SET NULL clause with a FOREIGN KEY constraint : FOREIGN KEY « Table « Oracle PL/SQL Tutorial





Specify that when a row in the parent table is deleted, the foreign key column for the row (or rows) in the child table is set to null.

SQL>
SQL> -- create demo table
SQL> create table Customer(
  2    id         NUMBER(3) primary key,
  3    NAME       VARCHAR2(15 BYTE)
  4  )
  5  /

Table created.

SQL>
SQL> create table account (
  2    id         NUMBER(3),
  3    type       VARCHAR2(20 BYTE)
  4  )
  5  /

Table created.

SQL>
SQL>
SQL> ALTER TABLE account
  2  ADD CONSTRAINT fk
  3  customerid REFERENCES customer(id) ON DELETE SET NULL;

Table altered.

SQL>
SQL>
SQL> drop table  account;

Table dropped.

SQL>
SQL> drop table customer;

Table dropped.

SQL>
SQL>
SQL>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