ON DELETE CASCADE

Question

ON DELETE CASCADE

JMI

SSChampion

Points: 11831
More actions
May 10, 2004 at 12:25 pm

#102003

ON DELETE CASCADE
Is it a good practice?
If I have lots of PK-FK relationships with "ON DELETE CASCADE"
it's not going to create any problems for my ASP application?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

IMO if you implement "ON DELETE CASCADE", you have to inform everybody who codes against your db who has write/delete permissions.

- Is it a good practise ? I'm not defaulting it to avoid unintended deletes. My default is still "on delete restrict".

- provide fk-indexes with columns in exact columnsequence and order (acs/desc) as defined at the parent object. ( this should be a default-behaviour anyway )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

tymberwyld SSCertifiable Points: 7810 More actions · Answer 2

It all depends on the scenario! For example, let's go with the classic example of Customers, Orders, and Order Details. Sure, I would probably put a Cascade Delete on the Orders to OrderDetails table. However, I would not put a Cascade Delete on the Customers to Orders tables because what if someone tried to delete a Customer, and "poof" all of the Order History is gone!

In this case, it's good not to have a Cascade Delete on the Customers table. You would want to get around this by prompting the User if they are absolutely sure they want to delete the Customer. If they choose yes, then you would delete all Orders first (which would cascade delete the Order Details), then you would delete the Customer. Also, not putting the Cascade Delete on the Customer table will stop anyone from accidently Deleting it in let's say, the Query Analyzer or Enterprise Manager.

I try to really think in-depth about what tables I put a Cascade Delete on. I try to only put them on tables with "Detail" information or information that really wouldn't be important (for example, a UserDefaults table).

JMI SSChampion Points: 11831 More actions · Answer 3

Tymberwyld,

I like people like you.

Detail oriented, passioned to help.

Thank you very much.

I got the idea.

Thanks a lot.

Alamir SSCertifiable Points: 6085 More actions · Answer 4

also take care that Cascade Delete is very dangerous (in depth)... because you may delete a row in a table and discover that there are many rows in many tables that are deleted !!

take care and use it initially with Tables and its details ... (think again if you need to do this )

I hope this help u

Alamir Mohamed
Alamir_mohamed@yahoo.com

JMI SSChampion Points: 11831 More actions · Answer 5

Thank you all!

I decide to use ON DELETE CASCADE

because this what I do in code anyway.

And I'm going to inform all in our group about that

and I will be careful.

Thanks a lot guys!

RobO