SQL Server can not delete proxies due to constraint violation
-------------------------------------------------------------
Key: NXP-8032
URL: https://jira.nuxeo.com/browse/NXP-8032
Project: Nuxeo Enterprise Platform
Issue Type: Bug
Components: Core SQL Storage
Affects Versions: 5.4.2
Environment: SQL Server
Reporter: Benoit Delbosc
There is no "ON DELETE CASCADE" for proxies.targetid FK, so removing a row in
hierarchy can not be done
if there the id is referenced by a proxy.targetid, this raises:
{code}
failsMsg 547, Level 16, State 0, Procedure nxTrigCascadeDelete, Line 5
The DELETE statement conflicted with the REFERENCE constraint
"proxies_targetid_hierarchy_fk". The conflict occurred in database "bdtest",
table "dbo.proxies", column 'targetid'.
{code}
This can be reproduced using unit test:
org.nuxeo.ecm.core.storage.sql.TestSQLBackend.testProxyDeepRemoval()
This is not possible to add the ON DELETE CASCADE because it adds a cyclic
cascade path that MSSQL don't support, fyi the msg is:
{code}
ALTER TABLE [proxies] ADD CONSTRAINT [proxies_targetid_hierarchy_fk] FOREIGN
KEY ([targetid]) REFERENCES [hierarchy] ON DELETE CASCADE;
...Introducing FOREIGN KEY constraint 'proxies_targetid_hierarchy_fk' on table
'proxies' may cause cycles or multiple cascade paths...
{code}
The proxies must be deleted before the hierarchy row using the
nxTrigCascadeDelete trigger.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
_______________________________________________
ECM-tickets mailing list
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
