Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shapper
Constraint Violating Yak Guru
450 Posts |
 Posted - 2007-10-18 : 07:11:25
|
Hello,I created a script to create a few tables but I am getting an error:Introducing FOREIGN KEY constraint 'FK_PostsComments_Comments' on table 'PostsComments' 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.What am I doing wrong?Here is my entire SQL script code: -- Users ...create table dbo.Users( UserID uniqueidentifier not null constraint PK_User primary key clustered, [Name] nvarchar(200) not null, Email nvarchar(200) null, UpdatedDate datetime not null) -- Blogs ...create table dbo.Blogs( BlogID uniqueidentifier not null constraint PK_Blog primary key clustered, Title nvarchar(400) null, Description nvarchar(2000) null, CreatedDate datetime null) -- Posts ...create table dbo.Posts( PostID uniqueidentifier not null constraint PK_Post primary key clustered, BlogID uniqueidentifier not null, AuthorID uniqueidentifier not null, Title nchar(1000) null, Body nvarchar(max) null, UpdatedDate datetime not null, IsPublished bit not null, constraint FK_Posts_Blogs foreign key(BlogID) references dbo.Blogs(BlogID) on delete cascade, constraint FK_Posts_Users foreign key(AuthorID) references dbo.Users(UserID) on delete cascade ) -- Comments ...create table dbo.Comments( CommentID uniqueidentifier not null constraint PK_Comment primary key clustered, AuthorID uniqueidentifier not null, Title nvarchar(400) null, Body nvarchar(max) null, UpdatedDate datetime not null, constraint FK_Comments_Users foreign key(AuthorID) references dbo.Users(UserID) on delete cascade )-- PostsComments ...create table dbo.PostsComments( PostID uniqueidentifier not null, CommentID uniqueidentifier not null, constraint PK_PostsComments primary key clustered (PostID, CommentID), constraint FK_PostsComments_Posts foreign key(PostID) references dbo.Posts(PostID) on delete cascade, constraint FK_PostsComments_Comments foreign key(CommentID) references dbo.Comments(CommentID) on delete cascade )go I believe this might be because of the constraint between Users table and one of the other tables.Just appreciated some feedback on this.Thanks,Miguel |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-10-18 : 18:57:40
|
| I know this is not about your actual question, but I'd take another look at using uniqueidentifier as a PK or at least as a CLUSTERED index. Also, from your schema a Comment can be associated with multiple Posts. Is that what you want? That may be the cause the the FK issue because if you delete a Post that has a comment also associatged with another Post it would delete that, Not sure if you want that or not. |
 |
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2007-10-19 : 05:56:22
|
| Hi,I don't need a comment to be associated with multiple posts.But I have other tables, such as Files and Events, which will also have comments associated with it.So I want to use only one Comments table.Each comment can be associated to ONE Post OR ONE Event OR ONE File.So I created the tables PostsComments, EventsComments and FilesComments which associate each Post, Event or File to a comment.Yes, I understand that in theory I can have multiple posts associated to a comment or even multiple posts, events and files associated to a comment but this is not going to happen.So maybe I am not creating my tables right?About the PK, I was reading various articles on using UniqueIdentifier or INT as PK and I also looked at the ASP.NET Membership SQL tables that are user since ASP.NET 2.0 and I thought UniqueIdentifiers were the better option.Why not?Thank You,Miguel |
|
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2007-10-19 : 06:07:22
|
In my code I think the error is in:create table dbo.Comments(CommentID uniqueidentifier not null constraint PK_Comment primary key,AuthorID uniqueidentifier not null,Title nvarchar(400) null,Body nvarchar(max) null,UpdatedDate datetime not null, constraint FK_Comments_Users foreign key(AuthorID) references dbo.Users(UserID) on delete cascade ***********************) Because if I delete a user the comment is deleted through Posts > PostsComments and directly through this association.So I deleted "on delete cascade" ***********************I think I am right.Thanks,Miguel |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-10-19 : 13:58:43
|
| I'm not saying GUIDs are bad, but you need to be aware of the ramifications of your decision. There are several things to consider. One of those things is size. A uniqueidentifier takes 16 bytes while an integer takes 4 bytes. Another issue has to do with how Clustered Indexes work. Here is a link to an article that talks about this sort of thing: [url]http://www.sql-server-performance.com/articles/per/guid_performance_p1.aspx[/url]As far as a comment being associated with other entities, you might want to ask here about how others would design it. With the current design I think you would need to add a unique constraint to the CommnetID on the PostComment table as a comment can only be associated with one and only one Post/Event/File. There are other options like creating a xxxCommnet table for associated a comment to another entity (for example EventComment or FileComment, then put EventCommnetiD in the Event table, etc). Or you might be able to use a subtype schema. etc. Sorry I don’t have more time to get some sample together. But, like I said if you ask some more questions I'm sure the fine people here will try to help you out.-Ryan |
|
|
|
|
|
|
|
|
Create Table. Contraint