ON DELETE CASCADE

ON DELETE CASCADE clause with a FOREIGN KEY constraint tells that when a row in the parent table is deleted, any matching rows in the child table are also deleted.


SQL> CREATE TABLE departments
  2  (department_id             number(10)        primary key,
  3   department_name           varchar2(50)      not null
  4  );

Table created.

SQL>
SQL> CREATE TABLE employees
  2  ( employee_id          number(10)      not null,
  3    last_name            varchar2(50)    not null,
  4    job_id               varchar2(30),
  5    salary               number(6),
  6    manager_id           number(6)
  7  );

Table created.

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

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: