David Caballero
David Caballero

Reputation: 73

JPA Composite Primary and Foreign keys that only share 1 field

I have the following DB model (all fields are not nullable):

File            SourceTypes
-----------     -----------
country PK      country PK
filename PK     sourcetype PK
sourcetype      sourcename

The sourcetype field in File references the SourceTypes table, however both tables have a composite PK, so the foreign key is also composite.

Source Type classes:

@Embeddable
class SoutceTypePk {
    String country
    String sourcetype
}

@Entity
class SourceType {
    @Id
    SourceTypePk id;
    
    String sourcename;
}

File classes:

@Embeddable
class FilePk {
    country
    filename
}

@Entity
class File {
    @Id
    FilePk id;

    @MapsId("country")
    @ManyToOne
    @JoinColumn(name="country", referencedColumn="country")
    @JoinColumn(name="sourcetype", referencedColumn="sourcetype")
    SourceType sourceType;
}

With this configuration, JPA excludes the sourcetype field from insert queries to the File table.

If I use MapsId without specifying the "country" field, I get errors that JPA expected a "sourcetype" column in the FilePK class.

If I take away the MapsId completely then I get "country" field is duplicated

How do I represent this scenario in my code with JPA annotations?

---- Update

The scenario here is that both tables have a composite PK, but the FK that exists in the File table only shares 1 field (country) with the PK of the sourcetype table.

The source type table is a catalog that contains country specific values, the file table contains country specific files that must meet a certain sourcetype for their country.

This model exists as described in our PostgreSQL database already and is working:

alter table sourcetype add primary key (country_cod, source_type_cod)
alter table file add primary key (country_cod, file_nam);
alter table file add foreign key (country_cod, source_type_cod) references sourcetype(country_cod, source_type_cod)

Our problem has been implementing that FK in JPA.

Upvotes: 0

Views: 34

Answers (0)

Related Questions