August 5, 2008 at 1:14 pm
I have include a few scripts to show what I am trying to do. The problem that I am having is the FK on table2 blowing up when I try to delete from table1. Table1 has a cascade delete on table2. Table2 has an FK back to itself. So what I did is create a trigger on table1 that deletes the children in table2. What I am doing does not seem to work. Can anyone tell me what I am doing wrong or a better solution?
CREATE TABLE [dbo].[Table1](
[Table1_Id] [uniqueidentifier] NOT NULL,
[Table1_Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Table1_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2](
[Table2_Id] [uniqueidentifier] NOT NULL,
[Table2_Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Table2_Id_Parent] [uniqueidentifier] NULL,
[Table1_Id] [uniqueidentifier] NULL,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Table2_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Table2] WITH CHECK ADD CONSTRAINT [FK_Table2_Table1] FOREIGN KEY([Table1_Id])
REFERENCES [dbo].[Table1] ([Table1_Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Table2_Table1]
GO
ALTER TABLE [dbo].[Table2] WITH CHECK ADD CONSTRAINT [FK_Table2_Table2] FOREIGN KEY([Table2_Id_Parent])
REFERENCES [dbo].[Table2] ([Table2_Id])
GO
ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Table2_Table2]
GO
CREATE TRIGGER [dbo].[trgdel_table1_Table2]
ON [dbo].[table1]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON;
--Deletes Children of Set if there are any
DELETE FROM Table2
WHERE [Table2_Id_Parent]
IN (SELECT [Table2_Id] FROM Table2 WHERE [Table1_Id] IN (SELECT [Table1_Id] FROM deleted))
--Deletes parent set
DELETE FROM Table2
WHERE [Table2_Id]
IN (SELECT [Table2_Id] FROM Table2 WHERE [Table1_Id] IN (SELECT [Table1_Id] FROM deleted))
END
GO
August 5, 2008 at 1:33 pm
I'm not sure exactly what you mean by "blowing up". However, I think the larger problem is knowing exactly how you expect things to work. If you have cascade delete in place, and then the table in which that cascade delete goes to beyond the original table is also self referencing, exactly what result do you expect? How is the cascade delete supposed to deal with the self referencing FK in table 2 ?
I think you need to reconsider the design, as your relationships within table 2 are currently subject to the vagaries of the cascade delete, and are apparently NOT protected, and thus you no longer have referential integrity. You'll want to consider exactly what needs deleting when a record in table1 is deleted. If you can't set up the table relationships such that a cascade delete doesn't mess things up, then you may need to turn off the cascade and start looking at a trigger. I'm not the expert on either referential integrity or triggers, but it just seems as though your cascade delete is unaware of the FK relationship, and I don't know enough to know if it's just a matter of configuration or if it's just not a practical configuration. Perhaps some of the more senior members can weigh in on this?
EDIT: P.S. to the gurus: Does the order of declaration of the constraints matter here?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 5, 2008 at 1:47 pm
I am at the same point. I am not sure about the order if I need to change the trigger so it fires before the FK. Or even if that is possible
1) I can not change table2 as far as design. I can change the FK to be not enforced and use triggers to maintain the table
2) From table2 cascade delete is turned on for table3 and .... which works.
If you have seen any of my other posts lately I am trying to work through a delete operation that spans almost the entire db. Delete Cascade is the only way I can make this work efficiently.
So to refine my previous post is there a way to change the order a FK/trigger is fired? So that I can delete table2?
August 6, 2008 at 9:20 am
I don't know what the solution is, but a self-referencing table within a cascade delete just doesn't sound to me like "the only efficient way". The details you've provided so far are really only a very small part of what might well be a design issue that "spans almost the entire database".
If you can provide more detail about at least the concept for the overall database design, there might be good reasons to provide entirely different advice. Getting the structure right for a database isn't always easy, but from what you've provided so far, we really don't have much to go on. It would be most helpful to understand the entities being represented in the various tables you have, so that one might compare that "reality" to one's experience and see if something clicks that might be helpful.
EDIT: See the following link in BOL on the effects of CASCADE DELETE, as it appears from the wording that a self-referencing table is not going to have that self-reference contribute to the knowledge on what to delete:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/54ca1d10-5106-4340-bde4-480b83e7e813.htm
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 6, 2008 at 9:45 am
I completely agree with the design flaw.
The first one was that they designed a database and then decided how they want to delete data.
The second one is that the rules in the user cases change since they are still somewhat being defined.
I have gotten around the issue of cascade delete by making true parent child relationships on the tables I could and the ones I could not I am going to take care of the issues with triggers. It is not the best solution since now we have to maintain triggers and bus. logic outside FK's. Going forward the design will reflect a better architecture to handle the user cases better.
I still have the question as to why a FK would stop you before a trigger on table1 would fire. Is there a sequence of events?
August 6, 2008 at 9:53 am
I'm pretty sure there is a specific sequence. See my EDIT in my previous post and see if you can find it from there. Also, I know you can change the order of triggers, but when things fire as opposed to when FK constraints take place is defiinitely something to refer to BOL on, and if I recall correctly, that page I linked to has some reference to when things fire.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply