Unique index created /w SQLServerPlatform cannot be used for FK #5507
Description
Bug Report
| Q | A |
|---|---|
| Version | 3.3.7 |
Summary
repro https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=36b1193feaaab2d8f54cb5d7aadd32c8
source of the issue
dbal/src/Platforms/SQLServerPlatform.php
Line 459 in 058c448
Current behaviour
FK constraint requires a foreign column to be unique. This is standard requirement across the DB vendors.
However SQL Server requires null value to be unique as well. This is atypicial for unique index and DBAL overcomes this behaviour by adding WHERE xxx IS NOT NULL to the unique index definition.
This extra condition on the unique index causes FK cannot be created. SQL Server is not smart enough to understand the IS NOT NULL is always satisfied with not nullable column.
How to reproduce
see links in the summary
Expected behaviour
The expected behaviour is unique index is created without any WHERE when the target column does not allow null values.
Only then FK can be created.
repro without WHERE xxx IS NOT NULL - FK is successfully added - https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=78d464488260d735f4d15aa42ae6f8e5