Friday, November 10, 2006

# 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)