NHibernate throwing SQL CE Error 25026

Question

I am using NHibernate with a SQL CE desktop database, and I'm getting an odd error when I try to do an update. SQL CE is throwing Error 25026: "A foreign key value cannot be inserted because a corresponding primary key value does not exist."

The exception occurs when performing a cascading update of a collection property of an entity object. The entity object is an Owner, and the collection property is Projects (IList), the projects for a particular Owner. In my database, the primary key of the Owners table is a three-character string (the owner's initials), with a corresponding foreign key in the Projects table.

Here's why I am puzzled: NHibernate can fetch all of the records for a particular owner (for example, "DCV"). And in my code, I can add a new Project object to Owner.Projects with no problem. I take the owner ID value directly from the Owner object fetched from the database, so I know the primary key exists in the Owners table. But when I do an ISession.SaveOrUpdate() on my Owner object, I get the foreign key error described above.

Am I dealing with some idiosyncracy of NHibernate, or some mundane error in my code or mappings? Any thoughts that would help me troubleshoot this problem greatly appreciated!

David Veeneman Foresight Systems

Answer 1

I found the answer. It has to do with how NHibernate handles one-to-many associations. From the NHibernate Documentation, Sec. 6.4, One-To-Many Associations:

Very Important Note: If the column of a association is declared NOT NULL, NHibernate may cause constraint violations when it creates or updates the association. To prevent this problem, you must use a bidirectional association with the many valued end (the set or bag) marked as inverse="true". See the discussion of bidirectional associations later in this chapter.

If you are having this problem, remove the foreign key constraint temporarily and run your code, outputting NHibernate's SQL to the console. You will see that NHibernate first inserts the new record without the foreign key, then calls up the record, then inserts the foreign key into the record. The first operation is what generates the foreign key error.

The solution, as the NHibernate documentation points out, is to make the relation bidirectional.