Uploaded image for project: 'OpenMRS Core'
  1. OpenMRS Core
  2. TRUNK-1925 Support Multiple Databases in OpenMRS Installation and Update
  3. TRUNK-2657

ON UPDATE CASCADE in relationship table cannot be performed on SQL Server

    XMLWordPrintable

    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.

        Attachments

          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: