How do I create a unique constraint that also allows nulls with fluent migrator?

Question

I want to have a unique constraint on a column which I am going to populate with GUIDs. However, my data contains null values for this columns. How do I create the constraint that allows multiple null values using fluent migrator without violating the unique constraint?

I found a workaround for SqlServer here. So it is possible to use FluentMigrator's IfDatabase and execute raw SQL for SqlServer. But I'd like to find a solution that is not system dependent.

SQLServer solution :

IfDatabase("sqlserver").Execute.Sql("
  CREATE UNIQUE NONCLUSTERED INDEX foo
  ON dbo.bar(key)
  WHERE key IS NOT NULL;
");

score 0 · Accepted Answer · answered Jul 08 '19 at 09:45

This feature - filtered indexes, partial indexes, etc. - is highly DBMS specific. For example, SQL Server and PostgreSQL have different syntaxes for it, while MySQL does not support this at all.

What follows from this is, since there exist DBMSs that do not support such a feature, FluentMigrator cannot provide such functionality in the general index creation interfaces. It does, however, provide a fluent interface for the thing you want to do for SQLServer explicitly as SqlServer.SqlServerExtensions.UniqueNullsNotDistinct.

How do I create a unique constraint that also allows nulls with fluent migrator?

1 Answers1