Why can't I reference child entities with part of the parent entities composite key

I am trying to reference some child entities with part of the parents composite key not all of it, why cant I? This happens when I use the following mapping instead of that which is commented.

I get the following error

Foreign key in table VolatileEventContent must have same number of columns as referenced primary key in table LocationSearchView

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="JeanieMaster.Domain.Entities" assembly="JeanieMaster.Domain">
  <class name="LocationSearchView" table="LocationSearchView">

    <composite-id>
      <key-property name="LocationId" type="Int32"></key-property>
      <key-property name="ContentProviderId" type="Int32"></key-property>
      <key-property name="CategoryId" type="Int32"></key-property>
    </composite-id>

    <property name="CompanyName" type="String" not-null="true" update="false" insert="false"/>
    <property name="Description" type="String" not-null="true" update="false" insert="false"/>
    <property name="CategoryId" type="Int32" not-null="true" update="false" insert="false"/>
    <property name="ContentProviderId" type="Int32" not-null="true" update="false" insert="false"/>
    <property name="LocationId" type="Int32" not-null="true" update="false" insert="false"/>
    <property name="Latitude" type="Double"  update="false" insert="false" />
    <property name="Longitude" type="Double"  update="false" insert="false" />

    <bag name="Events" table="VolatileEventContent" where="DeactivatedOn IS NULL" order-by="StartDate DESC" lazy="false" cascade="none">
      <key>
        <column name="LocationId"></column>
        <column name="ContentProviderId"></column>
        <!--<column name="LocationId"></column>
        <column name="ContentProviderId"></column>
        <column name="CategoryId"></column>-->
      </key>
      <one-to-many class="Event" column="VolatileEventContentId"></one-to-many>
    </bag>

  </class>
</hibernate-mapping>

And VolatileEventContent mapping file

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="JeanieMaster.Domain.Entities" assembly="JeanieMaster.Domain">
  <class name="Event" table="VolatileEventContent" select-before-update="false" optimistic-lock="none">
    <composite-id>
      <key-property name="LocationId" type="Int32"></key-property>
      <key-property name="ContentProviderId" type="Int32"></key-property>
    </composite-id>

    <property name="Description" type="String" not-null="true" update="false" insert="false"/>

    <property name="StartDate" type="DateTime" not-null="true" update="false" insert="false" />
    <property name="EndDate" type="DateTime" not-null="true" update="false" insert="false" />

    <property name="CreatedOn" type="DateTime" not-null="true" update="false" insert="false" />
    <property name="ModifiedOn" type="DateTime" not-null="false" update="false" insert="false" />

    <many-to-one name="Location" class="Location" column="LocationId" />

    <bag name="Artistes" table="EventArtiste" lazy="false" cascade="none">
      <key name="VolatileEventContentId" />
      <many-to-many class="Artiste" column="ArtisteId" ></many-to-many>
    </bag>
  </class>
</hibernate-mapping>

Solution

The error is correct. I'm guessing that you are using SchemaExport to generate your tables based on the NHibernate mappings, since the error you are receiving sounds like it would occur during the creation of your tables and foreign keys. SchemaExport would produce tables similar to the following (please note the explanations scattered throughout the code):

CREATE TABLE LocationSearchView (
    LocationId int NOT NULL,
    ContentProviderId int NOT NULL,
    CategoryId int NOT NULL,

    /* ...other columns... */

    /* Note: Generated from LocationSearchView's "composite-id" element.  */
    PRIMARY KEY (LocationId, ContentProviderId, CategoryId)
);

/* Note: Table for the "Event" class. */
CREATE TABLE VolatileEventContent (
    LocationId int NOT NULL,
    ContentProviderId int NOT NULL,

    /* ...other columns... */

    /* Note: Generated from Event's "composite-id" element. */
    PRIMARY KEY (LocationId, ContentProviderId),
    /* Note: Generated from the "key" element of LocationSearchView's Events bag. */
    FOREIGN KEY (LocationId, ContentProviderId) REFERENCES LocationSearchView (LocationId, ContentProviderId)
);

... hence the error. A foreign key must point to a complete primary or unique key - not just part of a primary key. The whole key is 3 columns, not 2. Why would NHibernate use those columns for the foreign key? Because of the <key> element of LocationSearchView's Events bag. <key> specifies which columns from the child point back to the parent.

Let's consider what might happen when you (or NHibernate) try to select from these tables. Assume the following data:

TABLE LocationSearchView
LocationId ContentProviderId CategoryId
========== ================= ==========
1          3                 5
1          3                 6
1          4                 5
1          4                 6
2          3                 5
2          3                 6
2          4                 5
2          4                 6

TABLE VolatileEventContent
LocationId ContentProviderId
========== =================
1          3
1          4
2          3
2          4

It is not possible for "one" LocationSearchView to have "many" Events. Rather, it should be the other way around. Given these tables, there is really a one-to-many relationship from Event to LocationSearchView.

I don't know what the correct solution to this problem is, because I don't know what you are trying to accomplish, but hopefully this helps to illuminate what exactly the problem is.