SQL Server Error 1785: Migration V2.9.0.20210423125133__assets_tags.sql fails with cascade delete conflict

[amaan-dev1]

Description

The Flyway migration V2.9.0.20210423125133__assets_tags.sql fails on SQL Server with Error 1785 due to multiple cascade delete paths on the tag_groups table.

Environment

• WebAPI Version: 2.8.1
• Database: Microsoft SQL Server 12.0 (Azure SQL Database)
• Database Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver (mssql-jdbc-10.2.1)
• Flyway Version: 4.2.0

Error Message

SQL State  : S0000
Error Code : 1785
Message    : Introducing FOREIGN KEY constraint 'tag_groups_tag_fk' on table 'tag_groups' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Location   : db/migration/sqlserver/V2.9.0.20210423125133__assets_tags.sql

Root Cause

The migration creates the tag_groups table with two foreign keys, both with ON DELETE CASCADE, both referencing the same parent table (tags):

CREATE TABLE webapi.tag_groups
(
    tag_id   INT NOT NULL,
    group_id INT NOT NULL,
    CONSTRAINT tag_groups_group_fk FOREIGN KEY (group_id) REFERENCES webapi.tags (id) ON DELETE CASCADE,
    CONSTRAINT tag_groups_tag_fk FOREIGN KEY (tag_id) REFERENCES webapi.tags (id) ON DELETE CASCADE
);

Steps to Reproduce

1. Set up fresh SQL Server database
2. Configure WebAPI 2.8.1 to connect to SQL Server
3. Enable Flyway migrations
4. Start application
5. Migration fails with Error 1785

Logs

ohdsi-webapi-debug-logs.txt

[chrisknoll]

Hello. We haven't supported the 2.8 version for several years, so to address this I would suggest making the necessary changes in your local environment to that flyway migration file so that it doesn't throw that error. As far as the tags and tag groups (I'm not sure what the FKs are on that table are intended to keep in sync) I can request a developer on our team to provide some guidance here.

@oleg-odysseus , can you please chime in on this issue and suggest a fix?

[oleg-odysseus]

Hi, as I see, @dsfrederic kindly migrated the scripts from Postgres which supports multiple cascade paths. And since SQL Server does not allow multiple ON DELETE CASCADE paths referencing the same parent table, I would propose to update the tag_groups table definition to use ON DELETE NO ACTION (or omit the ON DELETE clause, as NO ACTION is the default) and try running WebAPI. For example:

CREATE TABLE ${ohdsiSchema}.tag_groups
(
.....
CONSTRAINT tag_groups_tag_fk FOREIGN KEY (tag_id) REFERENCES ${ohdsiSchema}.tags (id) ON DELETE NO ACTION
);

Kindest Regards,
Oleg.

[chrisknoll]

Thank you @oleg-odysseus .

@amaan-dev1 , I think the simplest approach is to modify that .sql script directly in your local enviornment, rebuild and launch it. The flyway migration should pass after making the update FK's ON DELETE to NO ACTION for the tag_groups_tag_fk.