May 20, 2010 at 12:19 pm
I have a script that includes this chunk...
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FileAttributeCollectionValues_Files]') AND parent_object_id = OBJECT_ID(N'[dbo].[FileAttributeCollectionValues]'))
ALTER TABLE [dbo].[FileAttributeCollectionValues] WITH CHECK ADD CONSTRAINT [FK_FileAttributeCollectionValues_Files] FOREIGN KEY([FileID])
REFERENCES [dbo].[Files] ([FileID])
ON DELETE CASCADE
Notice the ON DELETE CASCADE. When we run this script everything seems to work just fine Except the actual Delete Rule property DOES NOT get changed to "Cascade" and instead stays as "no action".
Can anyone suggest what I might be missing here? I expected this property to change.
Thanks for any help you can offer.
May 21, 2010 at 1:14 pm
the syntax looks ok. Not sure why you're getting the error. Are you running with the latest service packs? I ran this on SQL 2005 Developer Edition SP3 and it created the FK with a cascade delete
CREATE TABLE [dbo].[Files](
[FileID] [int] NOT NULL,
CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED
(
[FileID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
go
CREATE TABLE [dbo].[FileAttributeCollectionValues](
[FileID] [int] NOT NULL,
[SomeDetail] [int] NOT NULL,
CONSTRAINT [PK_FileAttributeCollectionValues] PRIMARY KEY CLUSTERED
(
[FileID] ASC,
[SomeDetail] ASC
) ON [PRIMARY]
) ON [PRIMARY]
go
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FileAttributeCollectionValues_Files]') AND parent_object_id = OBJECT_ID(N'[dbo].[FileAttributeCollectionValues]'))
ALTER TABLE [dbo].[FileAttributeCollectionValues] WITH CHECK ADD CONSTRAINT [FK_FileAttributeCollectionValues_Files] FOREIGN KEY([FileID])
REFERENCES [dbo].[Files] ([FileID])
ON DELETE CASCADE
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 24, 2010 at 6:09 am
Mike01...
Thanks for the reply... We are also running the Developer Edition SP3. The code I shared is from the scripting tool to generate the database and yet no matter how many times I run it, or try to tweak and run it - same results - the cascades (every one of them!) do not come through.
Cant figure out for the life of me why this is happening, but today I am going to generate just a small script and see if the cascades get set correctly. Eg, best I guess to start working backwards to see if I can find whats going on.
Thanks!
May 24, 2010 at 6:38 am
We found the problem...
The script binds the update in an IF NOT EXIST clause and since the foreign key DID exist, no further code was run there. We tweaked the script a bit to drop the key, then re-establish it, and it works.
Guess this one is "pilot error"...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply