I have a hierarchy of tables in my database. They look like this:
That is, a one-to-many relation exists between reports and ReportMetrics, ReportTopN, and ReportComments. Each of the three child tables have foreign key references to a particular report.
I'm wondering how to set cascading on deletes.
I know that I can set the delete rule on foreign keys to Cascade in SSMS. I did it like so for the Report foreign key in ReportComments:
I did the same for the other two foreign keys. Then I tried to delete a report and it told me I couldn't because of a foreign key constraint. Then I opened the Foreign Key Relationships dialog box again and found that the delete rule had been set back to No Action. So it seems I can't set the delete rule to Cascade.
How does one set cascading on delete in SQL?