Current Behavior

The v4.13.0 migration fails for MSSQL, because the new APIKEY.PUBLIC_ID column has UNIQUE index. The column is only populated after the schema migration completes, and hence starts out with NULL values for existing records.

As per ANSI SQL, multiple NULL values are allowed in a UNIQUE index. All other RDBMSes respect this. MSSQL doesn't.

We cannot create partial indexes through DataNucleus to bypass this issue.

Not using a UNIQUE constraint for the PUBLIC_ID column is not an option.

Steps to Reproduce

1. Deploy DT <4.13.0 with MSSQL
2. Deploy 4.13.0-SNAPSHOT
3. Observe the migration failing due to:

   com.microsoft.sqlserver.jdbc.SQLServerException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.APIKEY' and the index name 'APIKEY_PUBLIC_IDX'. The duplicate key value is (<NULL>).
   

Expected Behavior

Find a way to make the migration work for MSSQL.

Dependency-Track Version

4.13.0-SNAPSHOT

Dependency-Track Distribution

Container Image

Database Server

Microsoft SQL Server

Database Server Version

No response

Browser

N/A

Checklist

• I have read and understand the contributing guidelines
• I have checked the existing issues for whether this defect was already reported