Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    1

    Unanswered: Foreign Key Constraint Issues -- msg 1785

    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.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    ON DELETE/UPDATE CASCADE are very dangerous things to use. I wouldn't feel comfortable if someone rings at my front door and my back door would automatically open. That is what a CASCADE does: doing something in one place changes things in another place.

    What does ON UPDATE CASCADE do?: When the PK in one table changes, all the FK's to that PK are changed accordingly. When would you ever need that in a well designed database?
    Use stable PK's in all your tables, ones that don't change over time, like IDENTITY (or GUID) columns. Use that in your database for PK-FK's. Define another "user_ID" column, next to your real PK, that you expose to your users. When they feel like changing it, they can do so without impact on the PK-FK's in your database. As the "User_id" is like any other attribute in your database, like a LastName or a SSN, stored only in one place. It only has to be updated in one place and there is no need for a CASCADE.

    You don't use ON DELETE CASCADE, that's good. It is something I would only use in a supertype/subtype relationship.
    Last edited by Wim; 11-21-11 at 04:36.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Wim View Post
    As the "User_id" is like any other attribute in your database ...
    but it isn't

    he wanted to use it in the purchase_order_line table as a foreign key to the merchandise table

    so even if the merchandise table changes its PK, the user_id in merchandise must still be declared unique -- and that's ~not~ like any other attribute

    another serious drawback of replacing user_id in purchase_order_line with a surrogate key, now you can't look at a row in the purchase_order_line table and know which user it belongs to, you have to perform an extra join

    so "like any other attribute" is not quite right
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by r937 View Post
    so "like any other attribute" is not quite right
    I was focussing on PK versus non-PK columns here. But you're right. One needs to declare a unique index on all user_id's as unique.

    another serious drawback of replacing user_id in purchase_order_line with a surrogate key, now you can't look at a row in the purchase_order_line table and know which user it belongs to, you have to perform an extra join
    I think we are diverting from the original question: how to handle the ON UPDATE CASCADE error, to a discussion about using surrogate keys or not.
    I know the pro's and con's of using surrogate keys and I am a fervent proponent of using them. Hiding the Id (real PK) from the users and keeping it internal to the database to handle the PK-FK relations and exposing a business-key (user_id) to the users that they may change and alter at will without touching the structure of the database is a big advantage.

    Suppose today the ITEM_ID is an INT, tomorrow they want to store far bigger numbers, you'd have to alter the MERCHANDISE table and all tables that have a FK to that table (using a BIGINT, DEC, ..). Next year they want to store characters too in that column, and you'd have to alter the MERCHANDISE table and all tables that have a FK to that table again.
    Compare that where you only have to change that column in the MERCHANDISE table.

    In my opinion it pales the "serious drawback" of having to perform an extra JOIN. That serious drawback is not worse than having to JOIN the PEOPLE table with the PURCHASE_ORDER table in order to get the name of the customer together with his order.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •