Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 874
Wahid Shalaly Avatar asked May 07 '09 13:05

Wahid Shalaly


2 Answers

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!

like image 116
Stefan Steinegger Avatar answered Sep 22 '22 21:09

Stefan Steinegger


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

like image 21
Jake Petroules Avatar answered Sep 23 '22 21:09

Jake Petroules