Skip to main content
Database Administrators

Return to Answer

Some wording improvements as well as spelling/typo fixes.
Source Link
D Kramer
D Kramer
  • 503
  • 3
  • 8

Aside from the aforementioned 'oops' where you delete a record you didn't intend to and now a lot of associated records are gone as well, you probably simply can't cascade delete everything.

A thing to keep in mind when using cascades is that it can cause conflicts if there's multiple cascade paths. SQL Server and other DBMS will block the creation of the foreign key with the cascade.

If you're going to delete cascade everything, you'll almost definitely hit this problem error and and either have to rework relations or be selective in whatwhich foreigns key actually does getsdo get cascades and which ones don't.

Explanation with illustrated example (not my source) Link

One way to get around it is to either use procedures/'front end' code (do the delete order management in the application calling the DB), or use triggers. Just try to be consistent with it. If your data model allows you to not hit multiple cascade paths, and you're certain you don't mind the 'oops'. Then by all means use it.

If you're going to hit multiple cascade paths, my advice would be to make the implementationimplementated solution consistent. Having some cascade, some triggers, some through procedures doing the management. Just makes the code disorganized.

Aside from the aforementioned 'oops' where you delete a record you didn't intend to and now a lot of associated records are gone as well, you probably simply can't cascade delete everything.

A thing to keep in mind when using cascades is that it can cause conflicts if there's multiple cascade paths. SQL Server and other DBMS will block the creation of the foreign key with the cascade.

If you're going to delete cascade everything, you'll almost definitely hit this problem error and either have to rework relations or be selective in what actually does gets cascades and which ones don't.

Explanation with illustrated example (not my source) Link

One way to get around it is to either use procedures/'front end' code (do the delete order management in the application calling the DB), or use triggers. Just try to be consistent with it. If your data model allows you to not hit multiple cascade paths, and you're certain you don't mind the 'oops'. Then by all means use it.

If you're going to hit multiple cascade paths, my advice would be to make the implementation consistent. Having some cascade, some triggers, some through procedures doing the management. Just makes the code disorganized.

Aside from the aforementioned 'oops' where you delete a record you didn't intend to and now a lot of associated records are gone as well, you probably simply can't cascade delete everything.

A thing to keep in mind when using cascades is that it can cause conflicts if there's multiple cascade paths. SQL Server and other DBMS will block the creation of the foreign key with the cascade.

If you're going to delete cascade everything, you'll almost definitely hit this problem and either have to rework relations or be selective in which foreigns key actually do get cascades and which ones don't.

Explanation with illustrated example (not my source) Link

One way to get around it is to either use procedures/'front end' code (do the delete order management in the application calling the DB), or use triggers. Just try to be consistent with it. If your data model allows you to not hit multiple cascade paths, and you're certain you don't mind the 'oops'. Then by all means use it.

If you're going to hit multiple cascade paths, my advice would be to make the implementated solution consistent. Having some cascade, some triggers, some through procedures doing the management. Just makes the code disorganized.

Source Link
D Kramer
D Kramer
  • 503
  • 3
  • 8

Aside from the aforementioned 'oops' where you delete a record you didn't intend to and now a lot of associated records are gone as well, you probably simply can't cascade delete everything.

A thing to keep in mind when using cascades is that it can cause conflicts if there's multiple cascade paths. SQL Server and other DBMS will block the creation of the foreign key with the cascade.

If you're going to delete cascade everything, you'll almost definitely hit this problem error and either have to rework relations or be selective in what actually does gets cascades and which ones don't.

Explanation with illustrated example (not my source) Link

One way to get around it is to either use procedures/'front end' code (do the delete order management in the application calling the DB), or use triggers. Just try to be consistent with it. If your data model allows you to not hit multiple cascade paths, and you're certain you don't mind the 'oops'. Then by all means use it.

If you're going to hit multiple cascade paths, my advice would be to make the implementation consistent. Having some cascade, some triggers, some through procedures doing the management. Just makes the code disorganized.