ON DELETE SET NULL

ON DELETE SET NULL clause with a FOREIGN KEY constraint tells that when a row in the parent table is deleted, the foreign key column for the rows is set to null.


CREATE TABLE departments
(department_id             number(10)        primary key,
 department_name           varchar2(50)      not null
);

CREATE TABLE employees
( employee_id          number(10)      not null,
  last_name            varchar2(50)    not null,
  job_id               varchar2(30),
  salary               number(6),
  manager_id           number(6)
);

SQL> ALTER TABLE employees
  2  ADD CONSTRAINT by_fk department_id
  3  REFERENCES departments(department_id) ON DELETE SET NULL;

Table altered.

SQL>
Home »
Oracle »
Table » 

Constraints:
  1. Adding a Constraint with CHECK
  2. Adding a NOT NULL Constraint
  3. Adding a FOREIGN KEY Constraint
  4. ON DELETE CASCADE
  5. ON DELETE SET NULL
  6. Adding a UNIQUE Constraint
  7. CHECK constraint
  8. Multiple Constraints
  9. Dropping a Constraint
  10. Disabling a Constraint
  11. Enabling a Constraint
  12. Deferred Constraints
  13. Getting Information on Constraints:user_constraints and all_constraints
Related: