nHibernate: Use one tabel in multiple many to one mappings

Question

Everything works fine for my SellDepartment. I can save and get the PriceLines but i get a foreign key constraint error when i try to save data to my SellEmployee tabel:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK9B5C4AB8B12F319A"

I know i can solve this by making a SellBase class which SellDepartment and SellEmployee then inherits from, but i really don't like to have an extra tabel in the database just for that.

So my quesstion is, can i solve this by just using mapping files? If yes, what am i doing wrong?

I have a tabel containing lines for different other tabels:

**PriceLines tabel**
PriceLineId
Price
Type
SellId

**SellDepartment tabel**
SellDepartmentId
Reason
DepartmentId

**SellEmployee tabel**
SellEmployeeId
Reason
EmployeeId

My nHibernate mapping file for PriceLines have these many-to-one mappings:

<subclass name="Source1Line" discriminator-value="DepartmentLine" extends="PriceLines">
  <many-to-one name="Source1" column="SourceId" class="Source1" />
</subclass>

<subclass name="Source2Line" discriminator-value="EmployeeLine" extends="PriceLines">
  <many-to-one name="Source2" column="SourceId" class="Source2"  />
</subclass>

And mapping files for my SellDepartment and SellEmployee:

<set name="PriceLines" cascade="all-delete-orphan" inverse="true">
  <key column="SellId"/>
  <one-to-many class="DepartmentLine"/>
</set>

<set name="PriceLines" cascade="all-delete-orphan" inverse="true">
  <key column="SellId"/>
  <one-to-many class="EmployeeLine"/>
</set>

Just a note: maybe it is just me and my problem but your simplified definition above is not clear. While I'd like to assit, have no idea what is all that about... — Radim Köhler

Low Flying Pelican Low Flying Pelican · Accepted Answer · 2014-06-23T00:58:24

Your database design does not seems right, the issue I can notice from the tables/mapping files is that,

Priceline.SellId can be either SellEmployeeId or SellDepartmentId, and from the information I have here, I assume those are primary keys.

So you have 2 constraints for the Priceline with both SellEmployee and SellDepartment, and when you insert a row to Priceline the constraints expect to have a relavent record in both SellEmployee and SellDepartment, and this is not the case. This is the reason why it fails.

nHibernate: Use one tabel in multiple many to one mappings

1 Answers