Updating a Foreign Key constraint with ON DELETE CASCADE not updating?

Question

We've realised in our SQL Server 2005 DB that some Foreign Keys don't have the On Delete Cascade property set, which is giving us a couple of referential errors when we try and delete some records.

Use the Management Studio I scripted the DROP and CREATESQL's, but it seems that the CREATE isn't working correctly.

The DROP:

USE [FootprintReports]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__SUBSCRIPTIONS_Reports]') AND parent_object_id = OBJECT_ID(N'[dbo].[_SUBSCRIPTIONS]'))
ALTER TABLE [dbo].[_SUBSCRIPTIONS] DROP CONSTRAINT [FK__SUBSCRIPTIONS_Reports]

and the CREATE

USE [FootprintReports]
GO
ALTER TABLE [dbo].[_SUBSCRIPTIONS]  WITH CHECK ADD  CONSTRAINT [FK__SUBSCRIPTIONS_Reports] FOREIGN KEY([PARAMETER_ReportID])
REFERENCES [dbo].[Reports] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[_SUBSCRIPTIONS] CHECK CONSTRAINT [FK__SUBSCRIPTIONS_Reports]

If I manually change the value of the On Delete in the GUI, after dropping and recreating, the On Delete isn't correctly updated.

As a test, I set the Delete rule in the GUI to Set Null. It dropped correctly, and recreated without error. If I got back into the GUI, it is still showing the Set Null as the Delete Rule.

Have I done something wrong? or is there another way to edit a constraint to add the ON DELETE CASCADE rule?

Answer 1

Your approach looks correct. ALTER TABLE is the only way to edit a constraint. Perhaps Management Studio is needs a data refresh. Try right-click Refresh, or close the app and reopen it.