ON DELETE SET NULL and ON DELETE CASCADE

ON DELETE SET NULL:

Incase if a row in the parent table is removed, Oracle will search the child table for all the matching rows and set foreign key column to null. This means that the rows will be orphaned but will still exist. If the column in the child table also have a not-null constraint, then the deletion from the parent table will fail.

ON DELETE CASCADE:

Incase if a row in the parent table is removed, Oracle will search the child table for all the matching rows and delete them too.

Note: It is not possible to drop or truncate the parent table in a foreign-key relationship, even there are now rows in the child table. This still applies if the ON DELETE SET NULL and ON DELETE CASCADE are used.