How to create a Multi-Column Index and/or Unique Constraint using NHibernate Mapping or Fluent NHibernate.
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!
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. Aunique-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".
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With