?
Solved

SQL Server 2005 throws multiple cascade paths error

Posted on 2008-06-19
11
Medium Priority
?
1,011 Views
Last Modified: 2012-05-05
The below code throughs the "may cause cycles or multiple cascade paths" error. What is strange is that it thinks that the fk_quo_proj and fk_quo_bidproj are referencing the same cascade path. I know this, because if I comment out line 4 (CONSTRAINT fk_Quo_proj ....) or Line 6 (CONSTRAINT fk_Quo_BidProj ...) it creates the table without issue. I know they have similiar names, but c'mon... I'm referenced 2 different table names with 2 diferent field names.... Any ideas guys? I don't want to implement triggers when this should work....

Note this is implemented in an application, but right now I'm just running this as a SSMS Query and I get the error...

I've posted a screenshot of how the database currently looks via SSMS Database Diagram (note this is with the constraint for the fk of bid_proj commented out!)

http://i57.photobucket.com/albums/g233/DustinSpears3/SQL_DB.jpg

Create Table  QUOTE (
QUOTE_ID integer PRIMARY KEY, 
PROJ_ID integer, 
CONSTRAINT fk_Quo_Proj FOREIGN KEY(PROJ_ID) REFERENCES PROJECT (PROJ_ID) ON UPDATE CASCADE ON DELETE CASCADE, 
BIDPROJ_ID integer, 
CONSTRAINT fk_Quo_BidProj FOREIGN KEY (BIDPROJ_ID) REFERENCES BID_PROJ (BIDPROJ_ID) ON UPDATE CASCADE ON DELETE CASCADE, 
TERMS_ID integer NOT NULL, 
CONSTRAINT fk_Quo_Terms FOREIGN KEY (TERMS_ID) REFERENCES TERMS (TERMS_ID) ON UPDATE CASCADE ON DELETE CASCADE, 
QuoteNum integer NOT NULL, 
QuoteAltNum integer NOT NULL, 
QuoteRevNum integer NOT NULL, 
QuoteDate datetime NOT NULL, 
QuoteCustPO VarChar(50), 
QuotePurchaser VarChar(100), 
QuoteIntNote VarChar(2000))

Open in new window

0
Comment
Question by:DustinSpears
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
11 Comments
 
LVL 19

Expert Comment

by:elimesika
ID: 21822542
HI

It seems as a feature , not a bug

see, http://support.microsoft.com/kb/321843

You will have to use triggers to achieve that.
0
 

Author Comment

by:DustinSpears
ID: 21822701
But it doesn't actually use multiple paths or cycle...? I don't reference the same table more than once in the create statement AND I don't have multiple tables lined up to cascade (the quote table is the only table with bid_proj table's pk set as fk).
0
 

Author Comment

by:DustinSpears
ID: 21822716
Ok, so I have never actually used triggers, where can I find a good straightforward guide to creating triggers in TSQL?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 19

Expert Comment

by:elimesika
ID: 21822807
You are right , I have created the following script and it runs without any errors on my SQL server
Here are mu version details , what version are you using (select @@version) ???

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Create Table  PROJECT (
PROJ_ID integer PRIMARY KEY, 
)
go
 
Create Table  BID_PROJ (
BIDPROJ_ID integer PRIMARY KEY, 
)
go
 
Create Table  TERMS (
TERMS_ID integer PRIMARY KEY, 
)
go
 
Create Table  QUOTE (
QUOTE_ID integer PRIMARY KEY, 
PROJ_ID integer, 
CONSTRAINT fk_Quo_Proj FOREIGN KEY(PROJ_ID) REFERENCES PROJECT (PROJ_ID) ON UPDATE CASCADE ON DELETE CASCADE, 
BIDPROJ_ID integer, 
CONSTRAINT fk_Quo_BidProj FOREIGN KEY (BIDPROJ_ID) REFERENCES BID_PROJ (BIDPROJ_ID) ON UPDATE CASCADE ON DELETE CASCADE, 
TERMS_ID integer NOT NULL, 
CONSTRAINT fk_Quo_Terms FOREIGN KEY (TERMS_ID) REFERENCES TERMS (TERMS_ID) ON UPDATE CASCADE ON DELETE CASCADE, 
QuoteNum integer NOT NULL, 
QuoteAltNum integer NOT NULL, 
QuoteRevNum integer NOT NULL, 
QuoteDate datetime NOT NULL, 
QuoteCustPO VarChar(50), 
QuotePurchaser VarChar(100), 
QuoteIntNote VarChar(2000))
 
go

Open in new window

0
 

Author Comment

by:DustinSpears
ID: 21823125
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)   Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
0
 

Author Comment

by:DustinSpears
ID: 21823237
Add a customer table to yours and see if somehow throws exception (you'll have to fk appropriately here is the code for those tables so you can copy paste.

NOTE: These are originally from an application, so you will have to probably run the create statement one at a time. Just do them in the order I have listed, and you will mimic the way I have it setup (that way you don' try to create a fk to a table that doesn't exist yet).
--company
Create Table COMPANY (COMP_ID integer PRIMARY KEY, DIV_ID integer, CompName VarChar(50) NOT NULL)
                        
--division
Create Table DIVISION (DIV_ID integer PRIMARY KEY, COMP_ID integer NOT NULL, CONSTRAINT fk_Div_Comp FOREIGN KEY (COMP_ID) REFERENCES COMPANY (COMP_ID) ON UPDATE CASCADE ON DELETE CASCADE, DivName VarChar(50) NOT NULL, DivStreet VarChar(200), DivCity VarChar(50), DivState VarChar(50), DivZip VarChar(25), DivPhone VarChar(25), DivFax VarChar(25))
                        
--employee
Create Table EMPLOYEE (EMP_ID integer PRIMARY KEY, DIV_ID integer NOT NULL, CONSTRAINT fk_Emp_Div FOREIGN KEY (DIV_ID) REFERENCES DIVISION (DIV_ID) ON UPDATE CASCADE ON DELETE CASCADE, EmpFName VarChar(50) NOT NULL, EmpLName VarChar(50) NOT NULL, EmpTitle VarChar(250), EmpActive VarChar(20) NOT NULL, EmpExt VarChar(20), EmpUserName VarChar(50), EmpPass VarChar(50), EmpAccess VarChar(50), EmpEst VarChar(20), EmpProjMgr VarChar(20), EmpPurch VarChar(20))
                        
--customer
Create Table CUSTOMER (CUST_ID integer PRIMARY KEY, CustName VarChar(100) NOT NULL, CustStreet VarChar(200), CustCity VarChar(50), CustState VarChar(50), CustZip VarChar(25), CustPhone VarChar(25), CustFax VarChar(25), CustType VarChar(25) NOT NULL)
                        
--vendor
Create Table VENDOR (VEN_ID integer PRIMARY KEY, VenName VarChar(100) NOT NULL, VenATTN VarChar(50), VenStreet VarChar(200) NOT NULL, VenCity VarChar(50) NOT NULL, VenState VarChar(50) NOT NULL, VenZip VarChar(25) NOT NULL, VenPhone VarChar(25) NOT NULL, VenFax VarChar(25) NOT NULL)
                        
--project
Create Table PROJECT (PROJ_ID integer PRIMARY KEY, CUST_ID integer NOT NULL, CONSTRAINT fk_Proj_Cust FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER (CUST_ID) ON UPDATE CASCADE ON DELETE CASCADE, ProjName VarChar(200) NOT NULL, ProjNum VarChar(25), ProjComsCode VarChar(25), ProjStatus VarChar(25) NOT NULL, ProjStreet VarChar(200), ProjCity VarChar(50), ProjState VarChar(50), ProjZip VarChar(25))
                        
--bid_proj
Create Table  BID_PROJ (BIDPROJ_ID integer PRIMARY KEY, CUST_ID integer NOT NULL, CONSTRAINT fk_BidProj_Cust FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER (CUST_ID) ON UPDATE CASCADE ON DELETE CASCADE, BidProjName VarChar(200) NOT NULL, BidProjStatus VarChar(25) NOT NULL, BidProjStreet VarChar(200), BidProjCity VarChar(50), BidProjState VarChar(50), BidProjZip VarChar(25))
                        
--quote
Create Table  QUOTE (QUOTE_ID integer PRIMARY KEY, PROJ_ID integer, CONSTRAINT fk_Quo_Proj FOREIGN KEY(PROJ_ID) REFERENCES PROJECT (PROJ_ID) ON UPDATE CASCADE ON DELETE CASCADE, BIDPROJ_ID integer, CONSTRAINT fk_Quo_BidProj FOREIGN KEY (BIDPROJ_ID) REFERENCES BID_PROJ (BIDPROJ_ID) ON UPDATE CASCADE ON DELETE CASCADE, TERMS_ID integer NOT NULL, CONSTRAINT fk_Quo_Terms FOREIGN KEY (TERMS_ID) REFERENCES TERMS (TERMS_ID) ON UPDATE CASCADE ON DELETE CASCADE, QuoteNum integer NOT NULL, QuoteAltNum integer NOT NULL, QuoteRevNum integer NOT NULL, QuoteDate datetime NOT NULL, QuoteCustPO VarChar(50), QuotePurchaser VarChar(100), QuoteIntNote VarChar(2000))

Open in new window

0
 
LVL 19

Accepted Solution

by:
elimesika earned 1500 total points
ID: 21823303
HI again

It seems that I have a later patch of the SQL Server , can you consider upgrading/installing the latetst SP from
http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&displaylang=en
0
 

Author Comment

by:DustinSpears
ID: 21823322
Yea it has something to do with the fact that both the project and bid project connect through the customer table. I eliminated references (fk) to the customer table and now the Quote creates without any errors. It thinks that I'm creating a cycle through the customer table, even though CUST_ID does not link to PROJ_ID or BIDPROJ_ID. It still thinks they are all the same cascade reference?
0
 

Author Comment

by:DustinSpears
ID: 21824749
I decided it was unnecessary to have a potential project table and a project table when I could just fill the project's status field with a value that represented it was still in bidding (i.e. we haven't won it yet). I'm guessing I'll run into a similiar issue elsewhere, at which point I'll post a new question and link to it from this one. I'll split the points between you guys.
0
 

Author Comment

by:DustinSpears
ID: 21824757
Oh, only one expert commented. No splitting, you just get all the points =D
0
 

Author Comment

by:DustinSpears
ID: 21825269
EDIT: In the end, I decided to remove all Cascading. My database only uses arbitrary integer values for all table ID's, so it's not like I have any primary keys that could potentially change anyways (was so focused on being thorough I didn't even realize this). It is my understanding that all a cascade does is if one ID number itself changes, it'll push the new id number in place of the old one throughout the tables using that id as a FK. I will never "change" an existing ID, so it's pointless to cascade. If I'm mistaken about how cascade works, please fill me in.
0

Featured Post

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question