NHibernate - Must Change the Save Order to Satisfy Database Constraints?

Question

Someone on our data team added a database constraint and, while it's perfectly valid and desirable, it creates great problems for NHibernate because there doesn't seem to be a way to override NHibernate's save order.

Given a (silly example) class like this:

public Person {
public virtual string FirstName { get; set; }
public virtual bool IsCurrent { get; set; }
}

and a constraint that only one record in the backing table can be "IsCurrent=true" at the same time . . .

If I try to "deprecate" an existing record by setting IsCurrent=false, and replace it with a new record with IsCurrent=true, I get an ADO exception on Save because NHibernate tries to perform the Insert first, violating the SQL Server constraint that only one record can be "IsCurrent=true" at once.

I see two options:

1) Can SQL Server be configured to check constraints only at the end of a transaction? The following statement (the "update" of the old row to "IsCurrent=false" would un-break the constraint.

2) Can NHibernate's save order (for instances of the same type) be modified or "hinted" in any way?

Thanks! Jeff

Answer 1

Either approach is possible; I would lean toward #2. If you call:

session.saveOrUpdate(person1);
session.flush();
session.saveOrUpdate(person2);

The flush will push the SQL statement to the database. I believe this will fix your problem. (The above is java Hibernate code, your syntax may vary slightly).

Answer 2

The problem here is that NHibernate is not aware of all the data intergity checks in the database layer. Your option 1 is possible if you hack SQL server and disable constraints for a (short) period when you manupulate data. But it is a dirty solution since constraints are disabled for all the transaction being processed at that time.

In this particular case I would use anonter approach: There are no integrity checks. Data integridy is based on firing trigger on insert or update. The trigger is responsible for setting IsCurrent to false for all relevant records except the record beung currently inserted or updated. Of course, you have to deal with recursive trigger firing since withing the trigger you are modifying records in the same table where trigger was fired.