locked
Problem setting Foreign Key Constraint RRS feed

  • Question

  • I realize this question has been asked several times over the internet but I can't seem to fully understand the issue and was hoping some expert here could explicitly explain the problem I'm having.  I have a table called NBB_Aliquots that contains several columns.  Two of which are called InsertUserID and UpdateUserID.  I want these two columns to reference a primary key in another table called NBB_Users.  This way I can keep track of who inserted the row and who updates the row.  I added the foreign key to the InsertUserID column just fine with the code below.

    -- ADD FOREIGN KEY
    ALTER Table NBB_Aliquots
    ADD CONSTRAINT FK_Aliquots_Users1
    FOREIGN KEY (InsertUserID) REFERENCES NBB_Users (UserID) ON UPDATE CASCADE;

    But when I try to add a foreign key to the UpdateUserID column using the code below I get this error "Introducing FOREIGN KEY constraint 'FK_Aliquots_Users2' on table 'NBB_Aliquots' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.".  

    -- ADD FOREIGN KEY
    ALTER Table NBB_Aliquots
    ADD CONSTRAINT FK_Aliquots_Users2
    FOREIGN KEY (UpdateUserID) REFERENCES NBB_Users (UserID) ON UPDATE CASCADE;

    I want referential integrity so if a UserID is deleted from NBB_Users and if that UserID exists in any other table I want to rollback that delete.  On the other hand if I I update a UserID I want all the referencing table rows to reflect the new UserID.  All my tables in my database will have InsertUserID and UpdateUserID.  I would prefer not to write triggers for every table to handle this problem.  I'm using SQL Server 2008 Pro.  Since I have 2008 I see that SQL Server could possibly do this for me using Change Tracking.  I'm a newbie to SQL Server and have briefly read about it.  Does anyone have any experience with Change Tracking?  If so, does this sound like a possible solution?

    Thanks,


    Ryan

    Friday, June 22, 2012 12:43 PM

Answers

  • Please check on the following URL

    http://msdn.microsoft.com/en-us/library/cc280462(v=sql.100).aspx


    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
    Happy Programming!

    • Marked as answer by Ryan0827 Friday, June 22, 2012 8:20 PM
    Friday, June 22, 2012 1:08 PM
  • This is a case where I would use a surrogate key, for example, an identity column, and use that surrogate key, not USERID to maintain the integrity you want.  For example, instead of a structure like

    CREATE Table NBB_Users(UserID varchar(20) Primary Key, <other columns>);
    CREATE Table NBB_Aliquots(InsertUserID varchar(20), UpdateUserID varchar(20), <other columns>);
    
    -- ADD FOREIGN KEY
    ALTER Table NBB_Aliquots
    ADD CONSTRAINT FK_Aliquots_Users1
    FOREIGN KEY (InsertUserID) REFERENCES NBB_Users (UserID) ON UPDATE CASCADE;
    -- ADD FOREIGN KEY
    ALTER Table NBB_Aliquots
    ADD CONSTRAINT FK_Aliquots_Users2
    FOREIGN KEY (UpdateUserID) REFERENCES NBB_Users (UserID) ON UPDATE CASCADE;

    which, as you note, you cannot do.  Create your table structure like

    CREATE Table NBB_Users(UserPK int identity Primary Key, UserID varchar(20) Unique Not Null, <other columns>);
    CREATE Table NBB_Aliquots(InsertUserPK int), UpdateUserPK int, <other columns>);
    
    -- ADD FOREIGN KEY
    ALTER Table NBB_Aliquots
    ADD CONSTRAINT FK_Aliquots_Users1
    FOREIGN KEY (InsertUserPK) REFERENCES NBB_Users (UserPK);
    -- ADD FOREIGN KEY
    ALTER Table NBB_Aliquots
    ADD CONSTRAINT FK_Aliquots_Users2
    FOREIGN KEY (UpdateUserPK) REFERENCES NBB_Users (UserPK);

    In this case, you have a new column UserPK which is used only to link the parent and child tables.  It is not ever displayed to the user.  Since the UserPK is never displayed to the endusers (indeed, they don't even know it exists), you never need to change its value.  So you don't need the ON UPDATE CASCADE clauses.  If the user wants to change UserID, they can do that with no problems.  If they try to delete it, and there are any rows in the NBB_Aliquots table, the delete will be rejected.

    Tom

    • Marked as answer by Ryan0827 Friday, June 22, 2012 8:20 PM
    Friday, June 22, 2012 2:19 PM
  • It's hard to see exactly what the problem is without seeing your structure.  What would be causing this error, is considering only those tables which have foreign keys constraints that have either the ON UPDATE CASCADE or ON UPDATE SET NULL option, there is no loop or multiple path, but when you add the new foreign key constraint, there is a loop or a multiple path.

    For example, suppose you have the following layout where the arrows indicate foreign key constraints with the ON UPDATE CASCADE option.

    That would be legal.  But you couldn't add any more ON UPDATE CASCADE foreign keys to these 4 tables.  Because any thing you added would violate either the loop rule or the multiple paths rule, That is

    Adding a new relationship from TableA to TableB creates multiple paths from A to B
    Adding a new relationship from TableA to TableC creates multiple paths from A to C
    Adding a new relationship from TableA to TableD creates multiple paths from A to D 
    Adding a new relationship from TableB to TableA creates a loop from A to A
    Adding a new relationship from TableB to TableC creates multiple paths from A to C
    Adding a new relationship from TableB to TableD creates multiple paths from B to D
    Adding a new relationship from TableC to TableA creates  a loop from A to A
    Adding a new relationship from TableC to TableB creates  multiple paths from A to B
    Adding a new relationship from TableC to TableD creates multiple paths from A to D
    Adding a new relationship from TableD to TableA creates  a loop from A to A
    Adding a new relationship from TableD to TableB creates  a loop from B to B
    Adding a new relationship from TableD to TableC creates multiple paths from A to C

    And in answer to your question about referencing the surrogate key you are creating for Users, I would do that.  Once you do that you have a key that never changes, so you don't need to do ON UPDATE CASCADE because the key you are using never changes.  And you avoid this problem.

    Even if you never have this problem - that is the foreign keys you want wouldn't cause this error if they were all ON UPDATE CASCADE, if you have a natural primary key that can change, then there is often a major advantage to using a surrogate key for the foreign keys.  The advantage is that if the natural key changes for a particular user, and you have used the natural key as the foreign key, then when you change the natural key of a row in the USERS table, SQL not only updates that row in the USERS table, it has to find and update all of the rows in all of the tables that directly or indirectly use that natural key in a foreign key relationship.  Depending on what is in your system structure and data, this can turn a simple update of one row into an update of many tables and many, many rows.  But if you are using a surrogate key for all foreign key relationships to the USERS table, then update the natural key value, SQL only changes the USERS row.

    Tom


    • Proposed as answer by Naomi N Monday, June 25, 2012 4:27 AM
    • Edited by Tom Cooper Monday, June 25, 2012 5:36 AM
    • Marked as answer by Ryan0827 Tuesday, June 26, 2012 12:38 AM
    Monday, June 25, 2012 2:37 AM

All replies

  • Please check on the following URL

    http://msdn.microsoft.com/en-us/library/cc280462(v=sql.100).aspx


    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
    Happy Programming!

    • Marked as answer by Ryan0827 Friday, June 22, 2012 8:20 PM
    Friday, June 22, 2012 1:08 PM
  • This is a case where I would use a surrogate key, for example, an identity column, and use that surrogate key, not USERID to maintain the integrity you want.  For example, instead of a structure like

    CREATE Table NBB_Users(UserID varchar(20) Primary Key, <other columns>);
    CREATE Table NBB_Aliquots(InsertUserID varchar(20), UpdateUserID varchar(20), <other columns>);
    
    -- ADD FOREIGN KEY
    ALTER Table NBB_Aliquots
    ADD CONSTRAINT FK_Aliquots_Users1
    FOREIGN KEY (InsertUserID) REFERENCES NBB_Users (UserID) ON UPDATE CASCADE;
    -- ADD FOREIGN KEY
    ALTER Table NBB_Aliquots
    ADD CONSTRAINT FK_Aliquots_Users2
    FOREIGN KEY (UpdateUserID) REFERENCES NBB_Users (UserID) ON UPDATE CASCADE;

    which, as you note, you cannot do.  Create your table structure like

    CREATE Table NBB_Users(UserPK int identity Primary Key, UserID varchar(20) Unique Not Null, <other columns>);
    CREATE Table NBB_Aliquots(InsertUserPK int), UpdateUserPK int, <other columns>);
    
    -- ADD FOREIGN KEY
    ALTER Table NBB_Aliquots
    ADD CONSTRAINT FK_Aliquots_Users1
    FOREIGN KEY (InsertUserPK) REFERENCES NBB_Users (UserPK);
    -- ADD FOREIGN KEY
    ALTER Table NBB_Aliquots
    ADD CONSTRAINT FK_Aliquots_Users2
    FOREIGN KEY (UpdateUserPK) REFERENCES NBB_Users (UserPK);

    In this case, you have a new column UserPK which is used only to link the parent and child tables.  It is not ever displayed to the user.  Since the UserPK is never displayed to the endusers (indeed, they don't even know it exists), you never need to change its value.  So you don't need the ON UPDATE CASCADE clauses.  If the user wants to change UserID, they can do that with no problems.  If they try to delete it, and there are any rows in the NBB_Aliquots table, the delete will be rejected.

    Tom

    • Marked as answer by Ryan0827 Friday, June 22, 2012 8:20 PM
    Friday, June 22, 2012 2:19 PM
  • >> I have a table called NBB_Aliquots that contains several columns. Two of which are called Insert_user_id and Update_user_id. I want these two columns to reference a primary key in another table called NBB_Users. <<
    :et's go back to basic data modeling and some of ISO-11179. “user” is the attribute; “id” is an attribute property. That is goo so far. The prefixes “update” and “insert” are roles; this is how an occurrence of a data element is used in a table. But those are not roles; those are verbs! We call them “data base events” in SQL, and they might fire triggers or DRI actions. 

    >> This way I can keep track of who inserted the row and who updates the row. <<
    I hope not! We do not put audit data in the table being audited. First of all, it is illegal, But more important, when a row is deleted all the history is deleted too. This audit data should be trapped at the system level and put into a separate audit database or file. 

    What little DDL you posted is also wrong. We never start a data element name with an underscore. That destroys your ability to use the name in ISO Standard software. 
    That is your real problem. The DRI cycle problem is the result of a bad schema design. Draw a diagram with boxes (tables) and arrows (REFERENCES). See the two arrows? That is a cycle. T-SQL is a little over cautious, but this is an NP-Complete problems. The classic example is three table cycle. Table A is referenced by B and C. But Table B is referenced by C. What happens to a row in C that gets different cascade from A and B at the same time? Oops! 

    If you keep this schema, you will just add kludge after kludge to it with triggers weird keys, triggers and other garbage. Throw it out and get some help on how to do DB audits. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, June 22, 2012 5:25 PM
  • I took your advice and added a surrogate key.  Now I've come across the same error but a slightly different situation.  My table NBB_Aliquots has a composite key made up of StudyID & AliquotRefID.  When I try to create a foreign key reference in NBB_AliquotAnalytes to reference the composite key I get the same error "Introducing FOREIGN KEY constraint 'FK_AliquotAnalytes_StudyIDAliquotRefID' on table 'NBB_AliquotAnalytes' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

    -- ADD FOREIGN KEY
    ALTER Table NBB_AliquotAnalytes
    ADD CONSTRAINT FK_AliquoteAnalytes_StudyIDAliquotRefID
    FOREIGN KEY (StudyID, AliquotRefID) REFERENCES NBB_Aliquots (StudyID, AliquotRefID) ON UPDATE CASCADE;

    So I guess there is no way to reference a composite key or any type of multicolumn constraint with ON UPDATE CASCADE?  If this is true, I don't see a point in even using a composite key if you can't take advantage of CASCADE (that sucks!)  Am I doing something wrong or am I stuck using another surrogate key?


    Ryan

    Sunday, June 24, 2012 11:16 PM
  • You can have ON UPDATE CASCADE on foreign key relationships with composite keys.  As the error message states, you cannot have ON UPDATE CASCADE if the new foreign key you are adding could cause loops, and/or multiple update paths to the same table - that's true whether or not you are using a composite key.

    So, for example, if you have a foreign key from TableA to itself, that foreign key cannot have ON UPDATE CASCADE because that would be a loop.  Or, if you have two foreign keys from TableA to TableB, at most one of them can have ON UPDATE CASCADE, if they both have ON UPDATE CASCADE, that could cause multiple updates (which was the problem you were having with your original post).  Or if there is a foreign key from TableA to TableB and a foreign key from TableB to TableC and a foreign key from Tablec to TableD and a foreign key from TableD to TableA, then you cannot make all 4 of those foreign keys ON UPDATE CASCADE since that would be a loop - you could have any 3 of them be ON UPDATE CASCADE, but not all four.

    The comments above also apply to ON UPDATE SET NULL, ON DELETE CASCADE and ON DELETE SET NULL foreign keys.

    Tom

    Monday, June 25, 2012 12:04 AM
  • I'm not sure if I fully understand the loop problem you are trying to explain when I run the code below.

    -- ADD FOREIGN KEY
    ALTER Table NBB_AliquotAnalytes
    ADD CONSTRAINT FK_AliquoteAnalytes_StudyIDAliquotRefID
    FOREIGN KEY (StudyID, AliquotRefID) REFERENCES NBB_Aliquots (StudyID, AliquotRefID) ON UPDATE CASCADE;

    I have three foreign keys in NBB_AliquotAnalytes:

    1.)  Column StudyID references the primary key in NBB_Studies.  Then column UserID in NBB_Studies references the primary key in NBB_Users.

    2.)  Column AnalyteCode references primary key in NBB_Analytes.  Then column UserID in NBB_Analytes references the primary key in NBB_Users.

    3.)  Column UserID references primary key in NBB_Users.

    I assume the loop problem lies within foreign key 1 & 3?  Every table in my database will have a column named UserID and it will always reference NBB_Users primary key.  I want to do this so I can see the user who has last updated each row in each table.  Is this bad database design?  If so, is there another way of keeping track of who updated the row last?

    I want to have ON UPDATE CASCADE and ON DELETE NO ACTION for all my foreign keys.  Should I reference a surrogate key in NBB_Users in all my tables?

    Thanks,  


    Ryan

    Monday, June 25, 2012 12:41 AM
  • It's hard to see exactly what the problem is without seeing your structure.  What would be causing this error, is considering only those tables which have foreign keys constraints that have either the ON UPDATE CASCADE or ON UPDATE SET NULL option, there is no loop or multiple path, but when you add the new foreign key constraint, there is a loop or a multiple path.

    For example, suppose you have the following layout where the arrows indicate foreign key constraints with the ON UPDATE CASCADE option.

    That would be legal.  But you couldn't add any more ON UPDATE CASCADE foreign keys to these 4 tables.  Because any thing you added would violate either the loop rule or the multiple paths rule, That is

    Adding a new relationship from TableA to TableB creates multiple paths from A to B
    Adding a new relationship from TableA to TableC creates multiple paths from A to C
    Adding a new relationship from TableA to TableD creates multiple paths from A to D 
    Adding a new relationship from TableB to TableA creates a loop from A to A
    Adding a new relationship from TableB to TableC creates multiple paths from A to C
    Adding a new relationship from TableB to TableD creates multiple paths from B to D
    Adding a new relationship from TableC to TableA creates  a loop from A to A
    Adding a new relationship from TableC to TableB creates  multiple paths from A to B
    Adding a new relationship from TableC to TableD creates multiple paths from A to D
    Adding a new relationship from TableD to TableA creates  a loop from A to A
    Adding a new relationship from TableD to TableB creates  a loop from B to B
    Adding a new relationship from TableD to TableC creates multiple paths from A to C

    And in answer to your question about referencing the surrogate key you are creating for Users, I would do that.  Once you do that you have a key that never changes, so you don't need to do ON UPDATE CASCADE because the key you are using never changes.  And you avoid this problem.

    Even if you never have this problem - that is the foreign keys you want wouldn't cause this error if they were all ON UPDATE CASCADE, if you have a natural primary key that can change, then there is often a major advantage to using a surrogate key for the foreign keys.  The advantage is that if the natural key changes for a particular user, and you have used the natural key as the foreign key, then when you change the natural key of a row in the USERS table, SQL not only updates that row in the USERS table, it has to find and update all of the rows in all of the tables that directly or indirectly use that natural key in a foreign key relationship.  Depending on what is in your system structure and data, this can turn a simple update of one row into an update of many tables and many, many rows.  But if you are using a surrogate key for all foreign key relationships to the USERS table, then update the natural key value, SQL only changes the USERS row.

    Tom


    • Proposed as answer by Naomi N Monday, June 25, 2012 4:27 AM
    • Edited by Tom Cooper Monday, June 25, 2012 5:36 AM
    • Marked as answer by Ryan0827 Tuesday, June 26, 2012 12:38 AM
    Monday, June 25, 2012 2:37 AM