Open lajones opened 7 years ago
Most stores allow multiple null
values. We compensate for this in SQL Server by adding a filter by convention to unique indexes on nullable columns.
Unique indexes (alternate keys) on nullable columns are where you'll see a difference between SQL Server and most other stores, but I don't think we allow optional keys, do we?
@bricelam #7956 was about separating the idea of an alternate key from a unique index. As of the fix for that bug we no longer assume a unique index implies an alternate key unless that unique index is on the principal end of an FK,
Currently it doesn't make sense for us to have a nullable key but @ajcvickers and @divega are discussing the implications if we did allow them (see the discussion at https://github.com/aspnet/EntityFramework/issues/7956#issuecomment-292428541).
To clarify, the idea here is that when the model has a nullable column that has a unique index on it, and that unique index does not contain the filter, then we should generate a warning. This likely requires #7938 to be implemented as a prerequisite. Also, this is not critical, so putting on the backlog for now.
Re unique indexes, vs. unique constraints and alternate keys, I think we need to work a bit on our ubiquitous language :smile:
A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.
Unique constraint = alternate key. unique index is just index with isUnique=true.
From the discussion with @ajcvickers of issue #7956, we should consider adding a warning in model validation if there is a unique index on a nullable property as you may see different behaviors for different providers.