Is it wise to set relationship between Categories and Articles tables Delete rule = cascade?

I think that with this dangerous operation by mistakenly deleting a category (even confirming the delete operation) will result a deletion of all articles in a category.

Maybe it's better to cascade to a default value like "Uncategorized"?
And also to add one or 2 SPs for deleting category and all alrticles by demand.
This will of course lower the performance but will increase capabilities.

You raise an excellent point.

Blocking deletes on categories which have articles assigned to them would probably be safest, if you intend to handle this at the database level.

There is a school of thought that holds that all referential integrity should be exposed by the DAL and not buried in the database at all, which I tend to agree with. If you did that, it would be easy to establish rules like what you've suggested:

"When deleting a category, if any Articles belong to that Category, set those Articles to Uncategorized".

__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

What should be the actual implementation?
I was thinking about 2 options:

1)Create a new category "Uncategorised" - make it protected against
deletion operation.Then assign id of this category as default value to
CategoryId column in Articles table.
Finally set the relationship delete rule to "Set Default"

2)Without creating any category, just assigning a default value(maybe 0 or
-999).
When articles are listed, we check if it assigned to category with id 0 (so
there is no matched category in category table for this id) and manually
set "Uncategorised" caption.


*Both options would have SP DeleteCategoryWithArticles(categoryId)

I think most blogs and such use option 1 or a variation thereof. There is usually an built-in default Uncategorized category (that cannot itself be deleted, of course) that any post will belong to unless it's assigned to a real category.

__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond