Is it possible to check a variable in a case statement then perform a select
query depending on the variable? What I have tried is something like this
which does not work. aNyone know how this can be accomplished?
declare @.@.var int, @.rtn int
set @.var = 3
select case @.var when 1 select @.rtn = catid from categories
when 2 select @.rtn = itmid from categories
when 3 select @.rtn = catmid from categorymixbjamin wrote:
> Is it possible to check a variable in a case statement then perform a sele
ct
> query depending on the variable? What I have tried is something like this
> which does not work. aNyone know how this can be accomplished?
>
> declare @.@.var int, @.rtn int
> set @.var = 3
> select case @.var when 1 select @.rtn = catid from categories
> when 2 select @.rtn = itmid from categories
> when 3 select @.rtn = catmid from categorymix
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
A CASE expression is like this:
CASE <condition>
WHEN <evaluation result 1>
THEN <true result> ELSE <false result>
WHEN <evaluation result 1>
THEN <true result> ELSE <false result>
.. etc. ...
END
Use IF:
IF @.var = 1
SELECT @.rtn = catid FROM categories
IF @.var = 2
SELECT @.rtn = itmid FROM categories
IF @.var = 3
SELECT @.rtn = catmid FROM categorymix
These statements assume that there is only 1 row in each table, which is
probably wrong. What are you really trying to do?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAy4TYechKqOuFEgEQKYbwCgzax4m6K6c5lD
CcRV3sxStCYnhlUAoPRg
pAwpcd7fD1BWfkIGeB21eKuG
=opZI
--END PGP SIGNATURE--|||bjamin (bjamin@.discussions.microsoft.com) writes:
> Is it possible to check a variable in a case statement then perform a
> select query depending on the variable? What I have tried is something
> like this which does not work. aNyone know how this can be
> accomplished?
There isn't any CASE statement in SQL. There is a CASE expression, which is
something different.
> declare @.@.var int, @.rtn int
> set @.var = 3
> select case @.var when 1 select @.rtn = catid from categories
> when 2 select @.rtn = itmid from categories
> when 3 select @.rtn = catmid from categorymix
Could write:
SELECT @.rtn = CASE WHEN 1 THEN (SELECT catid FROM categories)
WHEN 2 THEN (SELECT itmid FROM categories)
WHEN 3 THEN (SELECT catmid FROM categorymix)
EMD
Although, I think most people would prefer to use IF/ELSE in this case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Showing posts with label action. Show all posts
Showing posts with label action. Show all posts
Tuesday, March 20, 2012
Thursday, March 8, 2012
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,
Ryan
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 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 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:
BaseIDBaseName
1B1
2B2
3B3
Child1:
Child1IDBaseIDChild1Name
11C1
21C2
Child2:
Child2IDBaseIDChild2Name
11D1
22D2
Child1_2:
Child1IDChild2ID
11
12
21
22
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/subscriptions/managednewsgroups/default.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/subscriptions/support/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 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 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:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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.
> ================================================== ====
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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,
Ryan
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 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 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:
BaseIDBaseName
1B1
2B2
3B3
Child1:
Child1IDBaseIDChild1Name
11C1
21C2
Child2:
Child2IDBaseIDChild2Name
11D1
22D2
Child1_2:
Child1IDChild2ID
11
12
21
22
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/subscriptions/managednewsgroups/default.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/subscriptions/support/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 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 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:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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.
> ================================================== ====
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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 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:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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 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 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:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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/subscriptions/managednewsgroups/default.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/subscriptions/support/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.
======================================================
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 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:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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 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 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:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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/subscriptions/managednewsgroups/default.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/subscriptions/support/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.
======================================================
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.
========================================
==============
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.
========================================
==============
CASCADE DELETE (No Action)
Hi,
I have a table that I want to create a Foreign Key constraint on.
This column has NULL values.
I want to create the Foreign Key with a CASCADE DELETE NO ACTION.
I have done this through the script below as I am not sure if this can be do
ne through the GUI in Enterprise Manager. I CAN create a FK through the Ente
rprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box
"check existing data on cre
ation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI a
nd not check existing data on creation?
If not how can I modify my script below to not check the data on creating th
e Foreign Key as this is why I think my Script is not working.
Maybe I should have a Trigger instead?
Any advice/info is much appreciated.
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
(
[WKF_WORKFLOW_ID]
) REFERENCES [dbo].[T_NWKF_WORKFLOW] (
[WKF_WORKFLOW_ID]
)
ON DELETE NO ACTION
GO
Thanks,
C.On Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I have a table that I want to create a Foreign Key constraint on.
This column has NULL values.
I want to create the Foreign Key with a CASCADE DELETE NO ACTION.
I have done this through the script below as I am not sure if this can be do
ne through the GUI in Enterprise Manager. I CAN create a FK through the Ente
rprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box
"check existing data on cre
ation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI a
nd not check existing data on creation?
If not how can I modify my script below to not check the data on creating th
e Foreign Key as this is why I think my Script is not working.
Maybe I should have a Trigger instead?
Any advice/info is much appreciated.
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
(
[WKF_WORKFLOW_ID]
) REFERENCES [dbo].[T_NWKF_WORKFLOW] (
[WKF_WORKFLOW_ID]
)
ON DELETE NO ACTION
GO
Thanks,
C.On Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
CASCADE DELETE (No Action)
Hi,
I have a table that I want to create a Foreign Key constraint on.
This column has NULL values.
I want to create the Foreign Key with a CASCADE DELETE NO ACTION.
I have done this through the script below as I am not sure if this can be done through the GUI in Enterprise Manager. I CAN create a FK through the Enterprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box "check existing data on cre
ation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI and not check existing data on creation?
If not how can I modify my script below to not check the data on creating the Foreign Key as this is why I think my Script is not working.
Maybe I should have a Trigger instead?
Any advice/info is much appreciated.
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
(
[WKF_WORKFLOW_ID]
) REFERENCES [dbo].[T_NWKF_WORKFLOW] (
[WKF_WORKFLOW_ID]
)
ON DELETE NO ACTION
GO
Thanks,
C.
On Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
I have a table that I want to create a Foreign Key constraint on.
This column has NULL values.
I want to create the Foreign Key with a CASCADE DELETE NO ACTION.
I have done this through the script below as I am not sure if this can be done through the GUI in Enterprise Manager. I CAN create a FK through the Enterprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box "check existing data on cre
ation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI and not check existing data on creation?
If not how can I modify my script below to not check the data on creating the Foreign Key as this is why I think my Script is not working.
Maybe I should have a Trigger instead?
Any advice/info is much appreciated.
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
(
[WKF_WORKFLOW_ID]
) REFERENCES [dbo].[T_NWKF_WORKFLOW] (
[WKF_WORKFLOW_ID]
)
ON DELETE NO ACTION
GO
Thanks,
C.
On Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
CASCADE DELETE (No Action)
Hi
I have a table that I want to create a Foreign Key constraint on
This column has NULL values
I want to create the Foreign Key with a CASCADE DELETE NO ACTION
I have done this through the script below as I am not sure if this can be done through the GUI in Enterprise Manager. I CAN create a FK through the Enterprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box "check existing data on creation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI and not check existing data on creation
If not how can I modify my script below to not check the data on creating the Foreign Key as this is why I think my Script is not working
Maybe I should have a Trigger instead
Any advice/info is much appreciated
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
[WKF_WORKFLOW_ID
) REFERENCES [dbo].[T_NWKF_WORKFLOW]
[WKF_WORKFLOW_ID
ON DELETE NO ACTION
G
Thanks
COn Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I have a table that I want to create a Foreign Key constraint on
This column has NULL values
I want to create the Foreign Key with a CASCADE DELETE NO ACTION
I have done this through the script below as I am not sure if this can be done through the GUI in Enterprise Manager. I CAN create a FK through the Enterprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box "check existing data on creation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI and not check existing data on creation
If not how can I modify my script below to not check the data on creating the Foreign Key as this is why I think my Script is not working
Maybe I should have a Trigger instead
Any advice/info is much appreciated
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
[WKF_WORKFLOW_ID
) REFERENCES [dbo].[T_NWKF_WORKFLOW]
[WKF_WORKFLOW_ID
ON DELETE NO ACTION
G
Thanks
COn Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)