Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2679

SchemaTool ON DELETE CASCADE does not work with MSSQL

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 2.4
    • Component/s: None
    • Security Level: All
    • Labels:
      None
    • Environment:
      MSSQL 2008 R2

      Description

      The following queries are produced by:
      vendor\doctrine\orm\tests\Doctrine\Tests\ORM\Functional\SchemaTool\DDC214Test.php

      ALTER TABLE company_persons ADD CONSTRAINT FK_820EDD048EEC5B5C FOREIGN KEY (spouse_id) REFERENCES company_persons (id) ON DELETE CASCADE
      ALTER TABLE company_persons_friends ADD CONSTRAINT FK_EAD47FE9217BBB47 FOREIGN KEY (person_id) REFERENCES company_persons (id) ON DELETE CASCADE
      ALTER TABLE company_persons_friends ADD CONSTRAINT FK_EAD47FE96A5458E8 FOREIGN KEY (friend_id) REFERENCES company_persons (id) ON DELETE CASCADE
      ALTER TABLE company_employees ADD CONSTRAINT FK_899949F0BF396750 FOREIGN KEY (id) REFERENCES company_persons (id) ON DELETE CASCADE
      ALTER TABLE company_managers ADD CONSTRAINT FK_B1DEF56BBF396750 FOREIGN KEY (id) REFERENCES company_persons (id) ON DELETE CASCADE
      ALTER TABLE company_auctions ADD CONSTRAINT FK_6A41FC6DBF396750 FOREIGN KEY (id) REFERENCES company_events (id) ON DELETE CASCADE
      ALTER TABLE company_raffles ADD CONSTRAINT FK_9D157F46BF396750 FOREIGN KEY (id) REFERENCES company_events (id) ON DELETE CASCADE
      

      The errors:

      Msg 1785, Level 16, State 0, Line 1
      Introducing FOREIGN KEY constraint 'FK_820EDD048EEC5B5C' on table 'company_persons' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
      Msg 1750, Level 16, State 0, Line 1
      Could not create constraint. See previous errors.
      Msg 2714, Level 16, State 5, Line 1
      There is already an object named 'FK_EAD47FE9217BBB47' in the database.
      Msg 1750, Level 16, State 0, Line 1
      Could not create constraint. See previous errors.
      Msg 1785, Level 16, State 0, Line 1
      Introducing FOREIGN KEY constraint 'FK_EAD47FE96A5458E8' on table 'company_persons_friends' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
      Msg 1750, Level 16, State 0, Line 1
      Could not create constraint. See previous errors.
      Msg 2714, Level 16, State 5, Line 1
      There is already an object named 'FK_899949F0BF396750' in the database.
      Msg 1750, Level 16, State 0, Line 1
      Could not create constraint. See previous errors.
      Msg 2714, Level 16, State 5, Line 1
      There is already an object named 'FK_B1DEF56BBF396750' in the database.
      Msg 1750, Level 16, State 0, Line 1
      Could not create constraint. See previous errors.
      Msg 2714, Level 16, State 5, Line 1
      There is already an object named 'FK_6A41FC6DBF396750' in the database.
      Msg 1750, Level 16, State 0, Line 1
      Could not create constraint. See previous errors.
      Msg 2714, Level 16, State 5, Line 1
      There is already an object named 'FK_9D157F46BF396750' in the database.
      Msg 1750, Level 16, State 0, Line 1
      Could not create constraint. See previous errors.
      

      An explanation why this is happening:
      http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths

        Activity

        Hide
        Steve Müller added a comment - - edited

        This is a SQL Server limitation as it does not support multi-path cascades for foreign keys.
        See here: http://allyourdatabase.blogspot.de/2006/11/multiple-cascade-paths-error-in-sql.html

        Not sure what to do about this at the moment...

        Show
        Steve Müller added a comment - - edited This is a SQL Server limitation as it does not support multi-path cascades for foreign keys. See here: http://allyourdatabase.blogspot.de/2006/11/multiple-cascade-paths-error-in-sql.html Not sure what to do about this at the moment...
        Hide
        Flip added a comment -

        Yes there is nothing we can do to get this to work. But what we can do is:
        1. Build in some detection when this happens and then throw a php exception for this kind of error (yet to be created)
        2. All tests which rely on this functionality for SQL Server should be reviewed again. There are two possibilities:
        A. The test can be rewritten so that it doesn't have multiple paths.
        B. if not. The test has to be skipped for SQL Server.

        Show
        Flip added a comment - Yes there is nothing we can do to get this to work. But what we can do is: 1. Build in some detection when this happens and then throw a php exception for this kind of error (yet to be created) 2. All tests which rely on this functionality for SQL Server should be reviewed again. There are two possibilities: A. The test can be rewritten so that it doesn't have multiple paths. B. if not. The test has to be skipped for SQL Server.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Flip
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: