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?