Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Table

 

Use the ON DELETE SET NULL clause with a FOREIGN KEY constraint

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> ALTER TABLE account   2  ADD CONSTRAINT fk   3  customerid REFERENCES customer(id) ON DELETE SET NULL; Table altered. SQL> SQL> drop table  account; Table dropped. SQL> SQL> drop table customer; Table dropped. SQL> SQL>SQL> SQL>