November 20, 2011 at 8:30 pm
I have a created a database with nearly 20 tables in it, so I cannot post all of my statements. I am having an issue though with two tables' referential integrity when it comes to foreign key constraints. I want to constrain them to cascade updates from the primary key tables, but I keep getting the error msg 1785
--Introducing FOREIGN KEY constraint 'FK__PURCHASE___ITEM___64F971E5' on table 'PURCHASE_ORDER_LINE' 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 table with the primary key:
create table MERCHANDISE (
ITEM_ID varchar (8) NOT NULL PRIMARY KEY,
DESCRIPTION CHAR (60),
STANDARD_PRICE MONEY,
QUANTITY_ON_HAND INT,
REORDER_POINT INT,
VENDOR_ID DECIMAL (2,0) NOT NULL,
FOREIGN KEY(VENDOR_ID)REFERENCES VENDOR(VENDOR_ID)
ON UPDATE CASCADE ON DELETE NO ACTION);
Here is the table with the referential integrity problems:
create table PURCHASE_ORDER_LINE (
ORDER_LINE_NO DECIMAL (3,0) NOT NULL,
ORDER_NO DECIMAL (6,0) NOT NULL,
ITEM_ID VARCHAR (8),
UNIT_COST MONEY,
QUANTITY_ORDERED INT CHECK(QUANTITY_ORDERED>0),
QUANTITY_RECEIVED INT,
DATE_RECEIVED DATETIME DEFAULT GETDATE(),
PRIMARY KEY(ORDER_LINE_NO,ORDER_NO),
FOREIGN KEY(ORDER_NO)REFERENCES PURCHASE_ORDER(ORDER_NO)
ON UPDATE CASCADE ON DELETE NO ACTION,
FOREIGN KEY(ITEM_ID)REFERENCES MERCHANDISE(ITEM_ID)
ON UPDATE CASCADE ON DELETE NO ACTION);
And this SQL is followed by the error msg.
What can I do to work around this? I have tried making an update trigger but it does not work.
November 20, 2011 at 11:32 pm
I believe your database structure is like this.
CREATE TABLE GF
(
GF_ID INT, -- PK
DATA VARCHAR(100)
);
CREATE TABLE F
(
GF_ID INT, -- COMPOSITE PK, FK
F_ID INT, -- COMPOSITE PK
DATA VARCHAR(100)
);
CREATE TABLE C
(
GF_ID INT, -- COMPOSITE PK, COMPOSITE FK
F_ID INT, -- COMPOSITE PK, COMPOSITE FK
C_ID INT, -- COMPOSITE PK
DATA VARCHAR(100)
);
If not required remove composite PKs. Use Simple PKs and FKS.
CREATE TABLE GF
(
GF_ID INT, -- PK
DATA VARCHAR(100)
);
CREATE TABLE F
(
F_ID INT, -- PK
GF_ID INT, -- FK
DATA VARCHAR(100)
);
CREATE TABLE C
(
C_ID INT, -- PK
F_ID INT, -- FK
DATA VARCHAR(100)
);
Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths
November 21, 2011 at 12:12 am
What's the definition of PURCHASE_ORDER? Does it also have a foreign key to Merchandise?
If so, that's the problem as it means there are two paths from Purchase_Order_Line to Merchandise - direct and through Purchase_Order.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply