The quick answer is you can't use nullable composite foreign keys in this way.
With the help of a coworker however, we worked around the issue by changing perspective on the issue of tables owning portions of other tables, heres how for anyone else confused:
Step 1:
Create an @Embeddable style key class for any 1st derivative tables (for example, the C table in the question, which derives its S_Id from the S table's int PK) like:
@Embeddeable
@Access(value = AccessType.FIELD)
public class S_IdAndIdPK implements Serializable
{
private int id;
private int S_id;
... {constuctors and method overrides ommitted}
}
NOTE: It is unnecessary to create an embeddable object for the table S's ID since its SEntity is already using a basic @ID of type INT.
This is the key for the 1st derivative object. Use this newly created key as the @EmbeddedId of table C when you define the entity for table C. You can re-use this S_IdAndIdPK in any number of other tables which you would like to share this same database structure amongst.
Step 2:
Create an Embeddable object for the 2nd derivative table that does not extend but rather INCLUDES AS A VARIABLE the @Embeddable style key class previously created in step 1.
Step 3:
Add @AttributeOverride(name="id", column=@Column(name="my_col_new_name")) above your variable. This tells hibernate to use the database column called "my_col_new_name" instead of "id" but retains the fact that the original S_IdAndIdPK key object is a part of this "2nd derivative" entity's key which is needed to properly implement a @OneToMany mappedBy later. To summarize in an example, steps 2 and 3 should look something like this:
@Embeddable
@Access(value = AccessType.FIELD)
public class S_IdC_IdAndIndexPK implements Serializable
{
private int index;
@AttributeOverride(name = "id", column = @Column(name="parent"))
private S_IdAndIdPK parentPk;
... {constructors, getters and overrides omitted}
}
Step 4:
Now that the 1st derviative's key is properly included into the 2nd derivative's key, the 1st derivative entity may eager load via @OneToMany mappedBy relationships any 2nd derivative objects.
For example, if the entity DestinationEntity has its @EmbeddedID as S_IdC_IdAndIndexPK, CEntity may load all related Table I in the question (DestinationEntitys) when it is queried from the database:
@Entity
@Table(schema = "my_schema", name = "Cs")
@Access(value = AccessType.FIELD)
public class CEntity implements Serializable
{
@OneToMany(
cascade = CascadeType.ALL,
fetch = FetchType.EAGER,
mappedBy = "pk.parentPk", // look at DestinationEntity's pk.parentPk field to find which DestinationEntitys belong here
orphanRemoval = true)
@OrderBy("pk.index") // look at DestinationEntity's pk.index field to find order of objects that go into this array list
private final List<DestinationEntity> destinations = new ArrayList<>();
// Here is an example for a similar use case where instead
// of "index" a "name" column is used to
// automatically populate a HashMap for this entity of
// all its related Property (another derivative) objects.
// Property entity would do the same as Destination for including its primary key but instead of index use string "name" as the column name
@OneToMany(
cascade = CascadeType.ALL,
fetch = FetchType.EAGER,
mappedBy = "pk.parentPk", // look at pk.parentPk field of PropertyEntity, which is this C_entity's pk to find related PropertyEntitys
orphanRemoval = true)
@MapKeyColumn(name = "name") // look to the PropertyEntity's "name" field when populating related PropertyEntitys into this map.
private final Map<String, PropertyEntity> properties = new HashMap<>();
@EmbeddedId
private S_IdAndIdPK pk;
... {getters, setters, constructors, & overrides omitted}
}
The greatest advantage of this is the @Embeddable Pk's code is reusable for any other peer entity to 1st derivative objects, and each 2nd derivative only needs to override the attribute for the column name of "id" in the 1st derivative Primary Key. You can make there be any number of "id" foreign key fields for an object and it will not cause this concept to break when properly using @AttributeOverride.
You can then modify these CEntity Map and List data structures from POJO code, and when the parent object is done being changed and Hibernate save or update occurs, it will cascade all changes to any child 2nd derivative objects that are modified from among these 2 example collections into their respective tables. If any objects are removed from these collections, the orphanRemoval=true setting will cause them to automatically be deleted from their respective tables.
TL;DR:
Do not use 'extends' to extend any @EmbeddedId objects like S_IdAndIdPk by making something like an S_IdC_IdAndIndexPk extends S_IdAndIdPk because the id field may be re-appropriated in its purpose, and similarly do not use multiple @ID annotations either to bypass the fact that the object truly contains the key of another object. Also don't try to query the database for 2nd derivative objects anywhere other than from the objects that own them such as in the question where I attempted to use @ManyToOne. Instead make S_IdAndIdPk be @EmbeddedIds within any 2nd level derivative object's PKs such as S_IdC_IdAndIndexPk or S_IdC_IdAndNamePk, and use Attribute Overrides to map the original column names into your desired new column names in your 2nd derivative tables to avoid possible future collisions on the column name "id".
Hope this helps anyone else stuck on how to use Hibernate composite foreign keys properly for in-memory modeling of complex database relationships.
