Suppose I have a class Company with a property CompanyName that is supposed to be unique. I want to validate this before saving a new Company to the database. I can think of 2 possible approaches:

1) do a select prior to attempting to save the new Company. The problem with this approach is that there is a slight race condition, in that another user of the system may insert a Company of the same name in between the select and the insert operation.

2) place a unique constraint on the CompanyName column as follows:

<property name="CompanyName" column="CompanyName" unique="true"/>

This prevents duplicate company names from being inserted, but the generic exception that is thrown when the constraint is violated does not provide any means for the application to give feedback to user as to why the insert failed.

Does anyone have a good strategy for validating unique properties?

Thanks.

You are right with the generic exception. That's not of much use when it comes to user friendly error messages.

I think a combination of your two approaches fits best in most scenarios: Place a unique constraint and do the save (or update) without any check. If that fails (for whatever reason) you make a select to find out if the unique constraint may be violated. Based on the information you can give feedback to you users.

Thanks awerder for that suggestion. Doing the select after attempting the save/update avoids the race condition and is also more efficient.

I guess that's the best solution given the scenario, but why not submit a feature request in JIRA, asking NHibernate to throw a UniqueContraintViolationException in this case? Then you don't need to go back to the database just to determine what caused the insert to fail.

Hi,

This will not work. Let me explain. Performing query before of after update/insert doesn't change the fact that this query does not see changes done in other transactions until those are commited. This presents a conceptual obstacle to all workarounds that are implemented on an application level. In a concurrent environment it is likely that there will be two transactions saving same value and querying for duplicates at the same or near same time and getting validation done before they both commit. This will warrant that the same value is inserted twice or one of the sessions will get unique constraint violation if there is a DB unique constraint. The only workaround is to lock tables for concurrent modifications for entire period from validating to committing.

Your thoughts are appreciated.

Best regards,

- Vlad