Cascade DELETE

# cascade delete -- that is a database feature.
--
# validate foreign keys -- all should be (have to be) RI in the database as well.
# cascade update -- well, won't even go there
(which means you have an ill designed model in the first place as you
are updating a primary key -- meaning it is not really a primary key at all).
# for simple ins,upd,del,lck -- it is just as easy to code....., into Data API.

I don't like it(cascade delete) personally -- I'd rather have an error when I delete a parent
that has child records. Doesn't "seem" right to have that stuff just disappear.

It is useful in limited cases.
if and only if EVERY time you delete from parent you want to delete from child,
on delete cascade is OK.
but, if there are sometimes that is not the expected behavior -- don't do that, --such as customer and orders.
use a stored procedure/(Database API).

All access would be provided by PL/SQL APIs. I like this because:
It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loath triggers.

And, at last, make sure to index those foreign keys, else either approach would be slow slow slow.


You can only have on delete cascade when creating the constraint
(so you drop and recreate the constraint)

Cascade DELETE example:

drop table t1;
create table t1 ( x int primary key );
create table t2 ( y int primary key, x
references t1 on delete cascade );
create table t3 ( y references t2 on delete
cascade, x references t1 );


insert into t1 values ( 1 );
insert into t2 values ( 1 , 1 );
insert into t3 values ( 1 , 1 );

delete from t1;

select * from t1;
select * from t2;
select * from t3;

Formal way to add cascade delete constraint:

ALTER table c
add constraint c_fk_parent_table
foreign key(x) references p(x)
on delete cascade;