Hierarchical Cascades

Please let me know if this is the wrong forum to post in.

I am trying to set up a new database with Referential Integrity, but I can't get it to work. I was wondering if anyone had some helpful tips :)

In my test case I have 3 tables; WebSite, Author and Article.

WebSite is a parent to Author and Article.

Author is a parent to Article.

If I have all 3 UPDATE CASCADE it breaks. If I only have 2 (any 2) it works. I get that it's a circle, but each would only be one step (A change to Author should only change Article, and that should be it, nothing would happen to WebSite)

Why doesn't this work? How do I get around it?

Here is the error I get:

Introducing FOREIGN KEY constraint 'FK_Article_WebSite' on table 'Article' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Here is the SQL:

/**************** WebSite **************************/

CREATE TABLE [dbo].[WebSite](

[WebSiteID] [int] NOT NULL,

[URL] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_WebSite] PRIMARY KEY CLUSTERED

(

[WebSiteID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

/**************** WebSite **************************/

/**************** Author **************************/

CREATE TABLE [dbo].[Author](

[AuthorID] [int] NOT NULL,

[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[PersonalWebSiteID] [int] NULL,

CONSTRAINT [PK_Author] PRIMARY KEY CLUSTERED

(

[AuthorID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[Author] WITH CHECK ADD CONSTRAINT [FK_Author_WebSite] FOREIGN KEY([PersonalWebSiteID])

REFERENCES [dbo].[WebSite] ([WebSiteID])

ON UPDATE CASCADE

/**************** Author **************************/

/**************** Article **************************/

CREATE TABLE [dbo].[Article](

[ArticleID] [int] NOT NULL,

[Title] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[AuthorID] [int] NOT NULL,

[WebSiteID] [int] NOT NULL,

CONSTRAINT [PK_Article] PRIMARY KEY CLUSTERED

(

[ArticleID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[Article] WITH CHECK ADD CONSTRAINT [FK_Article_Author] FOREIGN KEY([AuthorID])

REFERENCES [dbo].[Author] ([AuthorID])

ON UPDATE CASCADE

ALTER TABLE [dbo].[Article] WITH CHECK ADD CONSTRAINT [FK_Article_WebSite] FOREIGN KEY([WebSiteID])

REFERENCES [dbo].[WebSite] ([WebSiteID])

ON UPDATE CASCADE

/**************** Article **************************/

Our update implementation has a restriction where you can not have multiple cascade paths that can impact the same row.

In English, you can't touch the same row twice with a cascading action. This restriction is actually a "good thing" because it allows your code to run a lot faster than it might otherwise. It does pose some restrictions on the schema design, but these can generally be handled with minor schema modifcations in most cases.

In your case, you have two paths to cascade to article. If your data is in a strict hierarchy, you can just have the path from website < author < article and skip the Website < Article path. Please try this.

Thanks,

Conor Cunningham

SQL Server Query Optimization Development Lead

|||

Thanks for the response.

I understand what you’re getting at; unfortunately I am little lost on how it applies to my issue. I am very new to databases, and I am sure I am missing something simple.

I do have author > website and article --> website, but they are unconnected relationships. An author has a personal website, and an article is posted on a website.... now the authors personal website _might_ be same as the articles website (such as a blog), but that doesn't have to be the case. An article could be posted on a news website, and the author’s personal website is just a bio, or maybe the author doesn't have a website.

So I get confused when you say website < author < article, because article could refer to a website that is not stored in author. How would this cascade?

I played around with your idea in SQL Management Studio and was unable to fix the issue. Could you give me some SQL, or tell me how to modify my SQL to make the system work correctly? I learn best by example :)

-Zach

p.s. Also, is there a tool to review what SQL statements the server is actually processing. Sometimes when I have bug in my ADO.NET code, it would be nice to review what the server thinks I am asking for.

|||Unfortunately there is no way to use cascading actions with your current schema design. The suggested change doesn't quite match your logical model. You need to implement the actions yourself using triggers if you stick with your current schema. Another method would be to have author_website and article_website tables and create a view that provides unified results. You can then use cascading FKs.