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

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

Start with db/migration/sqlserver/V2.9.0.20210423125133__assets_tags.sql and inspect the two foreign keys on webapi.tag_groups. Run the Flyway migration against a fresh SQL Server database to reproduce Error 1785, then verify the migration completes successfully without introducing an invalid cascade path.