0

I've got a column in a table that needs to be unique when a value is present, but should allow multiple NULL values. In SQL, I can do this using this answer, but is there a way to configure this using Fluent NHibernate?

            Map(x => x.UniqueProperty).Unique().Nullable();

...does not work, and creates an unfiltered unique constraint that does not allow multiple NULL values.

Community
  • 1
  • 1
GoatInTheMachine
  • 3,583
  • 3
  • 25
  • 35

1 Answers1

1

You cannot do that in NHibernate, that is, NHibernate won't let you create this kind of constraint. Yes, it is possible, for example, in SQL Server, if you create a unique index which does not apply to NULLS:

CREATE UNIQUE INDEX idx_UniqueProperty_notnull
ON dbo.T1(UniqueProperty)
WHERE UniquePropertyIS NOT NULL;
Ricardo Peres
  • 13,724
  • 5
  • 57
  • 74
  • Thats a shame...is there a good reason for this, or is it just a missing feature? – GoatInTheMachine Jan 05 '17 at 16:25
  • Well, I guess it's a missing feature... you can use auxiliary database objects in NHibernate (http://nhibernate.info/doc/nhibernate-reference/index.html), but I don't think there is a Fluent NHibernate method for it. I could be wrong, though! – Ricardo Peres Jan 05 '17 at 17:00
  • You can create the index in an hbm mapping by using the XML tag. So something like this: CREATE UNIQUE INDEX IX_Unique_Property ON Table(Property) WHERE Property IS NOT NULL DROP INDEX IX_Unique_Property ON Table Seems to work fine and produces the desired results. – John Sully Apr 20 '18 at 15:16