Updating a Foreign Key constraint with ON DELETE CASCADE not updating?
- by Alastair Pitts
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?
