Multiple Cyclical Cascade Paths Error

[aaron_martone]

Setting up relationships in MSSQL2005 on Win2K3 Standard.

Tables/Fields:

Posts

• post_slug (PK)
• post_status_eid (FK)

Comments

• comment_id (PK)
• comment_post_slug (FK)
• comment_status_eid (FK)

Entries

• entry_id (ID/Unique) (FK)
• entry_name

Relationships:

Posts.post_slug (PK) <-> (FK) Comments.comment_post_slug
On PK Delete -> Set FK to NULL
On PK Update -> Set FK to Cascade

Posts.post_status_eid (FK) <-> (FK) Entries.entry_id
On PK Delete -> Set FK to NULL
On PK Update -> Set FK to Cascade

Comments.comment_status_eid (FK) <-> (FK) Entries.entry_id
On Unique/ID Delete -> Set FK to NULL
On Unique/ID Update -> No Action

Can anyone tell me why I’m getting the following error: ?

[COLOR=DarkRed][B]‘Entries’ table saved successfully
‘Comments’ table

• Unable to create relationship ‘FK_comments_entries’.
  Introducing FOREIGN KEY constraint ‘FK_comments_entries’ on table ‘Comments’ 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.[/B][/COLOR]

[r937]

[aaron_martone]

Also, changing the Entries to Comments Delete Rule to NO ACTION and then adding the relationship of Posts.post_slug to Comments.comment_post_slug with Delete Rules of Set Default and Update Rules of Cascade NOW allows me to save everything without error.

But how come you instructed me to first changes the Entries to Comments Delete Rule to No Action? Wouldn’t this mean if I ever deleted an Entry that some Comments were using, those comments would still reference a non-existing Entry?

[r937]

try this –

ALTER TABLE dbo.am_comments
ADD CONSTRAINT FK_am_comments_am_posts
 FOREIGN KEY ( post_slug )
     REFERENCES  dbo.am_posts ( post_slug )
        ON UPDATE CASCADE
        ON DELETE SET DEFAULT

[aaron_martone]

But the Entries doesn’t cascade to Comments.

Entries.entry_id (PK) -> On Delete, Set Default | On Update, No Action -> (FK) Comments.comment_status_eid

[B]Entries.entry_id /B -> On Delete, Set Default | On Update, No Action -> (FK) Posts.post_status_eid

Posts.post_slug (PK) -> On Delete, Set Null | On Update, Cascade -> (FK) Comments.comment_post_slug

So for VERIFICATION, the UPDATE RULE runs on the Entries table even if I cannot update the PK, but instead update other fields?

(Also, each time you say “ENTITIES” do you mean my table “ENTRIES”?)

[aaron_martone]

Really?

I’ve heard about the horrible limitations that SQL has compared to mySQL, but this kinda takes the cake.

You’d think a relational DMBS would say “OK, if we update 1 field here, and 2 different tables reference that field, we can update both those tables.”

I guess what I’ll do here is in the admin control panel I build, when you delete a post, I’ll manually tell the programming language to do another delete, but this time delete all the comments to that post. Same thing with updates. They always tell you to let the DBMS do what it can before you have the server-side do it, but I guess we just gotta go manual here.

[r937]

@aaron_martone

Are you saying that the problem here is that my Posts.post_status_eid and its DELETE-SET NULL, UPDATE-CASCADE rules are conflicting with my Comments.comment_status_eid and its DELETE-SET NULL, UPDATE-CASCADE rules?

i imagine so, yes (i still don’t definitively know for a fact, because i can’t test your scenario for myself)

but the article you linked to says it rather plainly…[indent]You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree. [/indent]so if you have a cascade to comments from entities, and another cascade to comments from entitites via posts to comments, then the comments table appears more than once in the cascading referential actions tree that are triggered from an update or delete on the entities table

[aaron_martone]

TO the same table?

Again, I am confused.

As I understand it, an UPDATE RULE states that "If the PK field is UPDATED, then perform the action. If it is CASCADE, then it takes the new value for the PK and changes all records in the referencing table whose FK field contained the previous value and update it as well.

In a DELETE RULE, the same happens above, but instead of getting updated, the rows with matching FK values are DELETED.

If this is true, I can understand “multiple paths to same target” if the Entries.entry_id was a FK and a PK from Comments and PK from Posts were set to Cascade to that. But we’re going the other way around.

Are you saying that the problem here is that my Posts.post_status_eid and its DELETE-SET NULL, UPDATE-CASCADE rules are conflicting with my Comments.comment_status_eid and its DELETE-SET NULL, UPDATE-CASCADE rules?

In both cases, the FK is in Commens/Posts and the PK is in Entries. So wouldn’t monitored changes to the PK then propagate down the separate FKs?

[r937]

@aaron_martone

But the Entries doesn’t cascade to Comments.

you’ve lost me, sorry

can we start over?

how about scripting out all three tables again, along with the relationships

[aaron_martone]

RESTRICT. Yes. Such a keyword invokes thoughts of “DENIED!” rather than No Action’s invocation of thoughts of “Meh, I’ll ignore this.”

Wow. This all came down to a misinterpretation of a Rule Action. Rudy, you’ve been a champ helping me out here. Truly, your mind is a SQL Database of infinite knowledge and no Constraints Thanks again!

[aaron_martone]

In the database right now, the relationship diagram shows that I have 1 relationship between Entries and Comments and another between Comments and Posts.

I cannot setup the relationship between Posts and Entries without getting that aforementioned error.

Though the Posts table has no relationships with Entries (trying to get Posts.post_status_eid to relate to Entries.entry_id), there are 2 other fields in Posts that WILL have relationships to the same entry_id field, but those relationships have not yet been created.

They are Posts.post_category_eid (determines the category of the post) and Posts.post_commenting_eid (determines the commenting status of the post)

[r937]

without the ability to test any of this myself (i removed sqlserver from my machine some time ago) i can only guess

but my guess is that there are two paths from entity to comment that have cascade on them

[aaron_martone]

OK, I got the following error BUT I have a question that might be the cause of this issue.
First, to let you know, I removed all relationships between Entries/Posts, Entries/Comments and Comments/Posts… starting fresh.

Here is the error I got:

===================
‘entries’ table saved successfully
‘posts’ table

• Unable to create relationship ‘FK_posts_entries’.
  Introducing FOREIGN KEY constraint ‘FK_posts_entries’ on table ‘posts’ 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.
  ===================

Now, one place where I was possibly confused. In the Entries table I have a PK of Entries.entry_id. It is a SEEDED ID column, starting from 1, incrementing by 1. This PK has 2 FKs, 1 in the Posts.post_status_eid and the other in Comments.comment_status_eid. I think THIS is where I’m getting the error. On these relationships, I set the ON DELETE rule to SET NULL (the FK’s accept NULL values) But on UPDATE I told it to CASCADE.

Now, correct me if I’m wrong, but the PK Entries.entry_id can’t be updated anyways, right? So many I should set the ON UPDATES on both of those relationships to ‘DO NOTHING’?

In the end, there are 3 relationships here:

Entries.entry_id (PK) -> Comments.comment_status_eid (FK)
Entries.entry_id (PK) -> Posts.comment_status_eid (FK)
Posts.post_slug (PK) -> Comments.comment_post_slug (FK)

So when I see “may cause cycles” I can’t help but see how 1 table relates to the next relates to the first being a “cycle”.

[aaron_martone]

(Thank you for clarifying the “seeded identity” vs. just calling it the “identity”)

Well, I can see how I have 2 cascading paths…

One is from Entries.entry_id. Updates to it cascade to both the Posts.post_status_eid and Comments.comment_status_eid. But is it illegal to have 1 PK cascade to multiple FKs in a database design?

I don’t see any logical error. If I change an Entry.entry_id from 6 to 14, it would goto the Posts table, change all records with a post_status_eid of 6 to 14, and then goto the Comments table and change all records with a comment_status_eid of 6 to 14. Done and done.

Now, Posts and Comments link to each other. in a separate relationship between Posts.post_slug and Comments.comment_post_slug, but in my previous example, the changing of IDs from 6 to 14 wouldn’t do anything to change the post_slugs between Posts and Comments…

The error here HAS to be that the database simply doesn’t allow you to define more than 1 relationship on a PK that involves a Cascade… right?

[r937]

you’re doing fine, except for this bit –

When using automatically generated numbers (seeded), this value is created automatically but cannot be changed for updates or specified on inserts (though a record can be deleted).
see http://msdn.microsoft.com/en-us/library/ms188059.aspx for inserts, and for updates, try testing it and see what happens

foreign keys can reference either a primary key or a unique key

Because the value between these fields and records match, a relation is made between the tables.
actually, it’s the other way around – because a relationship exists between these columns, therefore the values will always match

[r937]

as for the cascades, the ON UPDATE and ON DELETE are triggered by updates and deletes on the referenced column, while the actions refer to the referencing column – you have understood the cascading part fine

[r937]

@aaron_martone

While I’m at that, is it OK in my example to use the varchar value of the post_slug between the Posts and Comments table where for the Posts table it is the PK and for the Comments it is the FK. I figure, there’s no point in having a seeded identity field called “post_id” on the Posts table because the post_slug is already maintaining unique record identification.

yes

your thinking behind this is right on the money

by the way, an identity column automatically increments starting with a seed value (which you can set but which defaults to 1), so there’s not much use in calling it a seeded identity as opposed to just an identity

[aaron_martone]

Is it illegal in DB design to have 2 relationships from 2 tables meet at the same field of another table?

I had the relationship from the Posts’s post_status_eid field on the Entry’s entry_id field before I tried to tie the Comment’s comment_status_eid to the Entry’s same entry_id field.

To me, I thought "OK, in this setup, if the Entry field is seeded and an identity, you HAVE to set the UPDATE action to NO ACTION (cause you cannot update the seeded value, right) But if DELETED, I wanted both the records in the Comments table that linked to it and the Posts records that linked to it to update to NULL (so in essence, the Posts would have no status and the Comments would have no status).

[aaron_martone]

POSTS TABLE

USE [test]
GO
/****** Object: Table [dbo].[posts] Script Date: 09/27/2010 10:41:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[posts](
[post_id] [int] IDENTITY(1,1) NOT NULL,
[post_slug] varchar NOT NULL,
[post_author] [int] NOT NULL,
[post_created] [datetime] NOT NULL CONSTRAINT [DF_posts_post_created] DEFAULT (getdate()),
[post_modified] [datetime] NULL,
[post_content] [text] NOT NULL,
[post_title] varchar NOT NULL,
[post_category] [int] NOT NULL,
[post_excerpt] varchar NOT NULL,
[post_comment_status] [int] NOT NULL,
[post_comment_count] [int] NOT NULL,
[post_status] [int] NOT NULL,
CONSTRAINT [PK_posts] PRIMARY KEY CLUSTERED
(
[post_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] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’unique record identifier.’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’posts’, @level2type=N’COLUMN’,@level2name=N’post_id’
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’slug line for the post.’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’posts’, @level2type=N’COLUMN’,@level2name=N’post_slug’
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’FK -> users.user_id - author of the post.’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’posts’, @level2type=N’COLUMN’,@level2name=N’post_author’
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’date and time the post was created.’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’posts’, @level2type=N’COLUMN’,@level2name=N’post_created’
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’date and time the post was last modified.’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’posts’, @level2type=N’COLUMN’,@level2name=N’post_modified’
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’content of the post.’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’posts’, @level2type=N’COLUMN’,@level2name=N’post_content’
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’title of the post.’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’posts’, @level2type=N’COLUMN’,@level2name=N’post_title’
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’FK -> entries.entry_id - the category of the post.’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’posts’, @level2type=N’COLUMN’,@level2name=N’post_category’
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’an excerpt for the post.’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’posts’, @level2type=N’COLUMN’,@level2name=N’post_excerpt’
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’FK -> entries.entry_id - the status of the post.’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’posts’, @level2type=N’COLUMN’,@level2name=N’post_comment_status’
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’the comment tally of the post.’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’posts’, @level2type=N’COLUMN’,@level2name=N’post_comment_count’
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’FK -> entries.entry_id - the status of the post.’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’posts’, @level2type=N’COLUMN’,@level2name=N’post_status’
GO
ALTER TABLE [dbo].[posts] WITH CHECK ADD CONSTRAINT [FK_posts_entries] FOREIGN KEY([post_category])
REFERENCES [dbo].[entries] ([entry_id])
GO
ALTER TABLE [dbo].[posts] CHECK CONSTRAINT [FK_posts_entries]
GO
ALTER TABLE [dbo].[posts] WITH CHECK ADD CONSTRAINT [FK_posts_entries1] FOREIGN KEY([post_comment_status])
REFERENCES [dbo].[entries] ([entry_id])
GO
ALTER TABLE [dbo].[posts] CHECK CONSTRAINT [FK_posts_entries1]
GO
ALTER TABLE [dbo].[posts] WITH CHECK ADD CONSTRAINT [FK_posts_entries2] FOREIGN KEY([post_status])
REFERENCES [dbo].[entries] ([entry_id])
GO
ALTER TABLE [dbo].[posts] CHECK CONSTRAINT [FK_posts_entries2]
GO
ALTER TABLE [dbo].[posts] WITH CHECK ADD CONSTRAINT [FK_posts_users] FOREIGN KEY([post_author])
REFERENCES [dbo].[users] ([user_id])
GO
ALTER TABLE [dbo].[posts] CHECK CONSTRAINT [FK_posts_users]

[aaron_martone]

My original script?

You know; I’m lost in a sea of DB development. Let me try a different tact.

Comments, Posts and Entries.

1 or more comments belong to 1 post

The Entries table list values associated with a Set (Like Set ID of 4 could be ‘US States’ so there would be 50 entries in the Entries table belonging to Set ID 4 for each State.

In both Comments and Posts tables, there are fields which link to the Entry table’s entry_id field:

Comments.comment_status_eid
and
Posts.post_status_eid

A comment could be approved/disapproved
A post can be published w/ comments, published w/o comments or not published.

When you DELETE a Post, I want the comment data that is associated with that post to set their FK to NULL.

When you UPDATE a post (the Post table uses a unique varchar ‘post_slug’), then I want the comments linking to that post via the post_slug to CASCADE on UPDATE.

When either Posts (status) or Comment (status) points to an Entry that is DELETED I want them to go to NULL. Since the Entry table’s PK uses a Identity that is seeded, the UPDATE has NO ACTION.

In this logic, I am flawed somewhere, since this cascading paths error comes up. I wish I could be more help but I think you’re talking above my level of understanding.