Is your feature request related to a problem? Please describe.
There is currently no good way to enforce uniqueness on nullable columns when you want two NULL values considered the same.
Describe the solution you'd like
For postgresql & recent versions of sqlite, use NULLS NOT DISTINCT, for other DB, compile queries to null aware operators and their equivalent and @@unique to partial/filtered unique indices.
Describe alternatives you've considered
Use some sentinel values that is not NULL on that column or some generated column. However, whether as an ad-hoc solution or plugin implemented by the user, or as a plugin/new feature added to zenstack itself, it's messy to work with due to a lack of a common null-ish value across various data types except for NULL, as well as need to insert dummy rows to satisfy referential integrity constraint on other tables when the said column is a foreign key.
Additional context
If any support for databases like SQL Server or Oracle were to be added, we should still retain the default defined behavior of considering nulls to be distinct by generating queries and filtered unique indices to enforce the inverse, rather than allow the whims of individual DBMS to define the behavior.
Is your feature request related to a problem? Please describe.
There is currently no good way to enforce uniqueness on nullable columns when you want two NULL values considered the same.
Describe the solution you'd like
For postgresql & recent versions of sqlite, use
NULLS NOT DISTINCT, for other DB, compile queries to null aware operators and their equivalent and@@uniqueto partial/filtered unique indices.Describe alternatives you've considered
Use some sentinel values that is not NULL on that column or some generated column. However, whether as an ad-hoc solution or plugin implemented by the user, or as a plugin/new feature added to zenstack itself, it's messy to work with due to a lack of a common null-ish value across various data types except for NULL, as well as need to insert dummy rows to satisfy referential integrity constraint on other tables when the said column is a foreign key.
Additional context
If any support for databases like SQL Server or Oracle were to be added, we should still retain the default defined behavior of considering nulls to be distinct by generating queries and filtered unique indices to enforce the inverse, rather than allow the whims of individual DBMS to define the behavior.