DDC-2679: SchemaTool ON DELETE CASCADE does not work with MSSQL #3417

Open
doctrinebot opened this Issue Sep 13, 2013 · 2 comments

2 participants

@doctrinebot

Jira issue originally created by user flip101:

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

@doctrinebot

Comment created by @deeky666:

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...

@doctrinebot

Comment created by flip101:

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.

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added this to the 2.4 milestone Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment