Details

    • Complexity:
      Low

      Description

      The person table

          person_id     name
          1           John
          2           Josh
      

      I have a relationship table

         relationship_id  person_a  person_b
         1                1         2
      

      I am creating a foreign key relationship between relationship.person_a -> person.person_id with ON UPDATE CASCADE. This works fine. Now I try to create a foreign key relationship between relationship.person_a -> person.person_id with UPDATE CASCADE, but I get an error from SQL Server as follows:

      SQL ALTER TABLE [dbo].[relationship] ADD CONSTRAINT [person_b_is_person] FOREIGN KEY ([person_b]) REFERENCES [dbo].[person] ([person_id]) ON UPDATE CASCADE:
      Introducing FOREIGN KEY constraint 'person_b_is_person' on table 'relationship' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
      

      This has been documented as a known issues in SQL Server: http://support.microsoft.com/kb/321843 as this can result in multiple cascade paths.

      Since, person_id is auto-generated, it is not updated and hence the update cascade is not required. Thus, although we can just make this change for SQL Server, to keep all databases engines in sync, we should remove that ON UPDATE CASCADE, but still keep the foreign key relationship.

        Gliffy Diagrams

          Attachments

            Attachments-Category-Modification

              Activity

                People

                • Assignee:
                  sunbiz Saptarshi Purkayastha
                  Reporter:
                  sunbiz Saptarshi Purkayastha
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: