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

        Flip created issue -
        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.

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-2679, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

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

            Dates

            • Created:
              Updated: