kotsumu kotsumu -4 years ago 148
SQL Question

NHibernate unique constraint remove if duplicate

Is there a way to tell nHibernate to remove the duplicate value on a row's column that is uniquely constrained when updating a row with a duplicate value.

For example

Id | Animal
1 | Dog
2 | Cat
3 | Bear

Updating Id 3 to Dog, should result in this

Id | Animal
1 |
2 | Cat
3 | Dog

Answer Source

I am quite sure there is no such feature in NHibernate, or any other ORM.

By the way, what should yield updating Id 3 to Cat after having updated it to Dog?

Id | Animal
1  | 
2  | 
3  | Dog

If that means that Id 1&2 now have the empty string value, that will be an unique constraint violation too.
If they have the null value, it depends then on the db engine being ANSI null compliant or not (null not considered equal to null). This is not the case of SQL Server, any version I know of, at least for the case of unique indexes. (I have not tested the unique constraint case.)

Anyway, this kind of logic, updating a row resulting in an additional update on some other rows, has to be handled explicitly.

You have many options for that:

  1. Before each assignment to the Animal property, query the db for finding if another one has that name and take appropriate action on that another one. Take care of flushing right after having handling this other one, for ensuring it get handled prior to the actual update of the first one.
  2. Or inject an event or an interceptor in NHibernate for catching any update on any entities, and add there your check for duplicates. Stack Overflow has examples of NHibernate events or interceptors, like this one.
    But your case will probably bit a bit tough, since flushing some other changes while already flushing a session will probably cause troubles. You may have to directly tinker with the sql statement with IInterceptor.OnPrepareStatement by example, for injecting your other update first in it.
  3. Or handle that with some trigger in DB.
  4. Or detect a failed flush due to an unique constraint, analyze it and take appropriate action.

The third option is very likely easier and more robust than the others.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download