Cascade Delete Error on FK

I'm getting the following error when I try to set the Delete action for a
relationship to Cascade. What I have is 4 tables. The base table, a child1
table, a child2 table, and a child1_mm_child2 table. For each Base object
(row) can have multiple child1 rows and multiple child2 rows. For a Base
object, for each child1 and child2 row there is a child1_mm_child2 row (in
other words an object with 2 child1 rows and 3 child2 rows would have 6
(2*3) child1_mm_child2 rows). What I want to do is when a Base object is
deleted, all related rows in all other 3 tables are deleted, hence the
Cascade On Delete relationships. I'm able to create all but 1 of the
relationships, the final one errors out.
"- Unable to create relationship 'FK_Sources_CIP'.
Introducing FOREIGN KEY constraint 'FK_Sources_CIP' on table 'Sources' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors."
Thanks for any assistance,
RyanHi Ryan,
Per my understanding, you were trying to create the tables like the
following:
CREATE TABLE [dbo].[Base] (
[BaseID] [int] IDENTITY (1, 1) NOT NULL ,
[BaseName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child1] (
[Child1ID] [int] IDENTITY (1, 1) NOT NULL ,
[BaseID] [int] NULL ,
[Child1Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child2] (
[Child2ID] [int] IDENTITY (1, 1) NOT NULL ,
[BaseID] [int] NULL ,
[Child2Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child1_2] (
[Child1ID] [int] NOT NULL ,
[Child2ID] [int] NOT NULL
) ON [PRIMARY]
GO
You wanted to append the cascade delete on the following relations:
FK_BASE_CHILD1
FK_BASE_CHILD2
FK_CHILD1_CHILD1_2
FK_CHILD2_CHILD1_2
The problem was that the last relation failed to be created.
If I have misunderstood, please let me know.
The behavior is expected if your tables were designed like the above. A
logically reasonable design should be that remove the relations
FK_CHILD1_CHILD1_2 and FK_CHILD2_CHILD1_2, add a column BaseID to the table
Child1_2, and create the relation FK_BASE_CHILD1_2 on cascade delete.
For example:
Base:
BaseID BaseName
1 B1
2 B2
3 B3
Child1:
Child1ID BaseID Child1Name
1 1 C1
2 1 C2
Child2:
Child2ID BaseID Child2Name
1 1 D1
2 2 D2
Child1_2:
Child1ID Child2ID
1 1
1 2
2 1
2 2
This is original design, but it is not reasonable. In this case, when you
execute "delete from Base where BaseID=1", what is your expected result?
Of course, the row (1,1,C1) and the row (2,1,C2) in Child1 should be
deleted, and the row (1,1,D1) in Child2 should be deleted; however should
the row (1,2) and the row (2, 2) in the table Child1_2 be deleted? or the
two rows should not have been existed in the table?
Hope this helps.
If you have any other quesitons or concerns, pleae feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
|||Charles,
You have it exactly right. It does look like I will need to add a FK to the
Child1_2 table for Base and apply the cascade delete relationship to that as
well. Thank you for your assistance.
Ryan
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:CJNRLDXVHHA.1580@.TK2MSFTNGHUB02.phx.gbl...
> Hi Ryan,
> Per my understanding, you were trying to create the tables like the
> following:
> CREATE TABLE [dbo].[Base] (
> [BaseID] [int] IDENTITY (1, 1) NOT NULL ,
> [BaseName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Child1] (
> [Child1ID] [int] IDENTITY (1, 1) NOT NULL ,
> [BaseID] [int] NULL ,
> [Child1Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Child2] (
> [Child2ID] [int] IDENTITY (1, 1) NOT NULL ,
> [BaseID] [int] NULL ,
> [Child2Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Child1_2] (
> [Child1ID] [int] NOT NULL ,
> [Child2ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> You wanted to append the cascade delete on the following relations:
> FK_BASE_CHILD1
> FK_BASE_CHILD2
> FK_CHILD1_CHILD1_2
> FK_CHILD2_CHILD1_2
> The problem was that the last relation failed to be created.
> If I have misunderstood, please let me know.
> The behavior is expected if your tables were designed like the above. A
> logically reasonable design should be that remove the relations
> FK_CHILD1_CHILD1_2 and FK_CHILD2_CHILD1_2, add a column BaseID to the
> table
> Child1_2, and create the relation FK_BASE_CHILD1_2 on cascade delete.
> For example:
> Base:
> BaseID BaseName
> 1 B1
> 2 B2
> 3 B3
> Child1:
> Child1ID BaseID Child1Name
> 1 1 C1
> 2 1 C2
> Child2:
> Child2ID BaseID Child2Name
> 1 1 D1
> 2 2 D2
> Child1_2:
> Child1ID Child2ID
> 1 1
> 1 2
> 2 1
> 2 2
> This is original design, but it is not reasonable. In this case, when you
> execute "delete from Base where BaseID=1", what is your expected result?
> Of course, the row (1,1,C1) and the row (2,1,C2) in Child1 should be
> deleted, and the row (1,1,D1) in Child2 should be deleted; however should
> the row (1,2) and the row (2, 2) in the table Child1_2 be deleted? or the
> two rows should not have been existed in the table?
> Hope this helps.
> If you have any other quesitons or concerns, pleae feel free to let me
> know.
> Have a good day!
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>|||Hi, Ryan,
Thanks for your updating and response.
I am very glad to hear that the suggestions are helpful. If you encounter
any issues in future, please feel free to post here.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============