How to create a Multi-Column Index or Unique Constraint with NHibernate

Question

How to create a Multi-Column Index and/or Unique Constraint using NHibernate Mapping or Fluent NHibernate.

Antitragus

Answer 1

assign a index/unique constraint name to more then one property

<property name="A" index="AB" />
<property name="B" index="AB" />

---

Theoretical it would also work with having more then one index on the same entity:

<property name="A" index="AB, ABC" />
<property name="B" index="AB, ABC" />
<property name="C" index="ABC" />

But there is a bug. I also wrote a patch. if you are interested in this, please vote for the bug or add comment or something.

Edit: just checked what happened to the bug. It is fixed in version 2.1.0, so it should perfectly work now. Many thanks to the great NHibernate developer team!

Scoutmaster

For Fluent NHibernate you have to use SetAttribute, which'd make it something like: Map(x => x.A).SetAttribute("index", "AB"); – Stifling

For anyone wondering, Fluent NHibernate now supports .Index("IndexName..."), rather than having to use SetAttribute. – Gyatt

It is important to say that the 'index' (and 'unique-key' too) property is used by NHibernate only when it generates a CREATE TABLE statement and doesn't prevent NHibernate from trying to insert a duplicate row when using session.Save etc. – Shoop

Answer 2

Old, but I have something to add since I came across this same issue:

Stefan Steinegger answered correctly for non-unique multi-column indexes, but left out the code for unique multi-column indexes. For those you can use:

<property name="A" unique-key="AB" />
<property name="B" unique-key="AB" />

So, essentially the same but with a different attribute name.

---

An interesting thing to note is that for unique indexes, NHibernate generates its own name for the key, but for non-unique indexes it uses whatever you give it.

For example, the above code will NOT generate a unique index named "AB", but rather something like UQ__TableName__7944C87104A02EF4.

This is documented in section 19.1.1 of the NHibernate documentation:

Some tags accept an index attribute for specifying the name of an index for that column. A unique-key attribute can be used to group columns in a single unit key constraint. Currently, the specified value of the unique-key attribute is not used to name the constraint, only to group the columns in the mapping file.

However the following:

<property name="A" index="AB" />
<property name="B" index="AB" />

will just generate an index named "AB".

Louden