SQL Server 2005 throws multiple cascade paths error

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

DustinSpearsAsked:
Who is Participating?
 
elimesikaConnect With a Mentor Commented:
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
 
elimesikaCommented:
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
 
DustinSpearsAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
DustinSpearsAuthor Commented:
Ok, so I have never actually used triggers, where can I find a good straightforward guide to creating triggers in TSQL?
0
 
elimesikaCommented:
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
 
DustinSpearsAuthor Commented:
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
 
DustinSpearsAuthor Commented:
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
 
DustinSpearsAuthor Commented:
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
 
DustinSpearsAuthor Commented:
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
 
DustinSpearsAuthor Commented:
Oh, only one expert commented. No splitting, you just get all the points =D
0
 
DustinSpearsAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.