15

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

Wahid Shalaly
  • 2,047
  • 1
  • 18
  • 29

2 Answers2

17

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!

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
  • 3
    For Fluent NHibernate you have to use SetAttribute, which'd make it something like: Map(x => x.A).SetAttribute("index", "AB"); – James Gregory May 07 '09 at 15:13
  • 1
    For anyone wondering, Fluent NHibernate now supports `.Index("IndexName...")`, rather than having to use `SetAttribute`. – Phill Jul 15 '11 at 01:08
  • 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. – tymtam Sep 22 '11 at 01:39
14

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".

Community
  • 1
  • 1
Jake Petroules
  • 23,472
  • 35
  • 144
  • 225