Solved

SQL Server 2005 throws multiple cascade paths error

Posted on 2008-06-19
11
1,002 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
  • 8
  • 3
11 Comments
 
LVL 19

Expert Comment

by:elimesika
Comment Utility
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
Comment Utility
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
Comment Utility
Ok, so I have never actually used triggers, where can I find a good straightforward guide to creating triggers in TSQL?
0
 
LVL 19

Expert Comment

by:elimesika
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:DustinSpears
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Oh, only one expert commented. No splitting, you just get all the points =D
0
 

Author Comment

by:DustinSpears
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now