ORM and Database Constraints

Question

How compatible is ORM and existing databases that have a lot of constraints (particularly unique key constraints/unique indexes beyond primary keys) enforced within the database itself?

(Often these are preexisting databases, shared by numerous legacy applications. But good database modeling practice is to define as many constraints as possible in the database, as a double-check on the applications. Also note that the database engine I am working with does not support deferred constraint checking.)

The reason I am asking is that the ORMs I have looked into, NHibernate and Linq to SQL, don't seem to hold up very well in the presence of database unique constraints. For example, deleting a row and re-inserting one with the same business key results in a foreign key exception. (There are subtle, harder to avoid examples as well.) The ORMs observe primary key and foreign key constraints, but tend to be oblivious to unique constraints.

I understand that there are workarounds, such as the NHibernate flush method. However, I feel this is an extremely leaky abstraction and makes it hard to design the application with regards to a separation of concerns. Ideally, all of the objects can be manipulated in memory by subroutines and then the main routine can take responsibility for the call to actually sync the database. This isolates the update and allowes for custom logic to inspect all of the updates before they are actually submitted to the database.

Executing the commands in the correct order is non-trivial. See my question here. Nonetheless, I was expecting better support for the common cases among the popular ORMs. This seems so important for introducing an ORM into an existing environment.

What have been your experiences with using ORM technologies is light of these issues?

Answer 1

This is of course IMHO...

ORM in general treats databases as merely a storage medium for data and is geared towards maintaining the constraints/business logic in the "O" side and not the "R" side. I haven't seen any ORM products that make use of some of the more "hardcore" relational database concepts like alternate keys, composite unique indexes, and exclusive subtypes. In a sense, ORM makes the database a second class citizen.

Call me old fashioned, but ORM seems to be good for reading data but for writing data back to a non-trivial relational design, I've always found it falls short. I prefer to do all my updates through SQL and/or stored procedures.

Answer 2

Good ORMs, and NHibernate is one, will enforce referential integrity and proper order execution if the database is mapped correctly. As far as I know, none of them support check or unique constraints. Check constraints are business rules that should be enforced in the business objects. I usually only enforce critical business rules (i.e. the business would lose money and/or I would lose my job if these rules were violated) in the database using check constraints and/or triggers.

Unique constraints usually represent an alternate key. With ORMs, it's common practice to use a surrogate key (identity) as the primary key and enforce a unique constraint on the natural key. It would be challenging for an ORM to implement unique constraint checking because it would require a select and lock before every insert or update. In general, the best practice is to always perform operations in a transaction that can be rolled back if it fails and provide a meaningful error message to the user.

For example, deleting a row and re-inserting one with the same business key results in a foreign key exception.

Were you trying to do this in the scope of a single ISession? I could see that being problematic.