multiple unique keys in nhibernate

Question

I need constraints created in the following manner:

CONSTRAINT [IX_Unique_1] UNIQUE NONCLUSTERED 
(
    [Ordering] ASC,
    [Description] ASC
),
CONSTRAINT [IX_Unique_2] UNIQUE NONCLUSTERED 
(
    [Description] ASC
)

I have the following nHibernate mapping:

<property name="Description" column="Description" type="String" unique-key="IX_Seed_Template_Fields_Result" />

<property name="Ordering" column="Ordering" type="Int32" unique-key="IX_Seed_Template_Fields_Result" />

So how can I add a separate unique constraint just for the Description column?

isn't the first constraint useless, due to the second one ? – mathieu Aug 30 '11 at 09:14 — mathieu, Aug 30 '11 at 09:14

score 4 · Accepted Answer · answered Aug 30 '11 at 09:20

If you don't care about the index names in the database, you can map it like this:

<property 
  name="Description" 
  column="Description" 
  type="String" 
  unique-key="Description, Ordering_Description" />

<property 
  name="Ordering" 
  column="Ordering" 
  type="Int32" 
  unique-key="Ordering_Description" />

you can provide a comma separated list of index names. All columns which have the same name in the list are added to the same index.

score 2 · Answer 2 · answered Oct 28 '10 at 11:42

2

Use <database-object> to create additional indexes.

5.6. Auxiliary Database Objects

answered Oct 28 '10 at 11:42

Diego Mijelshon

52,548
16
116
154

so if I use this I need to manually write the CREATE INDEX statement? – H77 Oct 29 '10 at 01:33
Why are indexes created manually? You just can specify the index attribute in the mapping files. – Stefan Steinegger Aug 30 '11 at 09:10
@Stefan: I didn't know you could write multiple index names in the `unique-key` attribute. If that works, your answer is better than mine (I upvoted it). Not sure if it will make a difference after 10 months, but... :-) – Diego Mijelshon Aug 30 '11 at 14:41
@Diego: It didn't work some time ago, because of a bug. It was most probably a feature from the original Hibernate code base which the NHibernate developers didn't know about :-) I fixed it and they integrated it to the core. Now it works. Using OSS is just cool. See http://stackoverflow.com/questions/834565/how-to-create-a-multi-column-index-or-unique-constraint-with-nhibernate – Stefan Steinegger Aug 31 '11 at 09:00

multiple unique keys in nhibernate

2 Answers2

Linked