I'm trying to implement class inheritance hieararchy with NHibernate and SQL server. I made it work fine with the table per hierarchy strategy (one table and several subclass elements in the mapping file).
However the table per subclass strategy (N + 1 tables and N joined-subclass elements in one mapping file) makes more sense in my case because I want to put some constraints on the subclass tables. I'm struggling with what kind of PK/FK association I should put on the masterclass table (Persons) and the subclass table (ContactPersons). According to the NHibernate documentation, there should be a primary key association between the tables. I tried to put the FK constraint from the subclass PK to the master class PK but it still doesn't work. I get the following exception:
NHibernate.Exceptions.GenericADOException: could not insert: [Jobflow.Models.Entities.ContactPerson][SQL: INSERT INTO ContactPersons (Email, Company, PersonID) VALUES (?, ?, ?)] —> System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'ContactPersons' when IDENTITY_INSERT is set to OFF.
I did try to set the IDENTITY_INSERT to ON but that did not seem to help…
Best Answer
You need to declare the column in Persons as int identity, but in ContactPersons as just int - only one table needs to keep track of the identity value. Your foreign keys/primary keys are fine, this is the only issue you need to tackle.
NHibernate will insert into Persons, get the new identity value using SCOPE_IDENTITY(), and then use that value explicitly for the insert into ContactPersons.