JPA: many-to-many relationship with @JoinTable having the same column for each side of relationship

Question

I would like to have a JoinTable between 2 entities, this two entities are derived from the same owner entity. So when joining this entities using @ManyToMany relationship I have got @JoinTable (like in below DDL):

 CREATE TABLE IF NOT EXISTS `local_services`.`service_provided_on` (
  `provider_id` INT UNSIGNED NOT NULL,
  `service_id` INT UNSIGNED NOT NULL,
  `service_point_no` INT UNSIGNED NOT NULL,
  `work_station_no` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`provider_id`, `service_id`, `service_point_no`, `work_station_no`),
  INDEX `fk_provider_service_has_work_station_work_station1_idx` (`service_point_no` ASC, `work_station_no` ASC, `provider_id` ASC),
  INDEX `fk_provider_service_has_work_station_provider_service1_idx` (`provider_id` ASC, `service_id` ASC),
  CONSTRAINT `fk_service_provided_on_provider_service`
    FOREIGN KEY (`provider_id` , `service_id`)
    REFERENCES `local_services`.`provider_service` (`provider_id` , `service_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_service_provided_work_station`
    FOREIGN KEY (`service_point_no` , `work_station_no` , `provider_id`)
    REFERENCES `local_services`.`work_station` (`service_point_no` , `work_station_no` , `provider_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

As you can see there are 2 Foreign Keys and each of them is using the same provider_id column. I would like to define using this @JoinTable, Services provided by given Provider on belonging to it WorkPlace (WorkStation). It will be obvious that service provided by provider with id ex. 5 can be only provided on workplace belonging to provider with id 5. So the best will be to share this @JoinColumn between each ForeignKeys. And when trying for example inserting workplace/service with not matching provider id to raise some exception!

I try to do something like this:

  @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "service_provided_on",
            joinColumns = {
                    @JoinColumn(name = "provider_id", referencedColumnName = "provider_id", nullable = false, columnDefinition = "BIGINT UNSIGNED"),
                    @JoinColumn(name = "service_id", referencedColumnName = "service_id", nullable = false, columnDefinition = "INT UNSIGNED")
            },
            inverseJoinColumns = {
                    @JoinColumn(name = "provider_id", referencedColumnName = "provider_id", insertable = false, updatable = false),
                    @JoinColumn(name = "service_point_no", referencedColumnName = "service_point_no", nullable = false, columnDefinition = "INT UNSIGNED"),
                    @JoinColumn(name = "work_station_no", referencedColumnName = "work_station_no", nullable = false, columnDefinition = "INT UNSIGNED")
            }
    )

But it obviously doesn't work and raise exception like this:

Caused by: org.hibernate.MappingException: Repeated column in mapping for collection: pl.salonea.entities.WorkStation.providedServices column: provider_id"}}

I consider renaming for one foreign key this provider_id ex. work_station_provider_id but then I will be allowed to insert not matching provider_ids and maybe I could define some CONSTRAINT to block such behaviour (how to define this in JPA?). It can work but I will have redundant column with the same provider_id

Answer 1

In some ways, mapping your model this way does not quite make sense. There is nothing in your many-to-many relationship that guarantees that the Services and Locations in the relationship must have the same Provider. I would change your object model to make that requirement explicit. Add a one-to-many relationship between Provider and a new object ServiceProvidedOn (not the greatest name; maybe something like ServiceOccurrence?). So Provider would have a collection attribute serviceProvidedOns; and the new class ServiceProvidedOn would have three attributes provider, service, and workStation (mapped appropriately). The primary key for ServiceProvidedOn would be derived from the three other objects (Provider, Service, and WorkStation).

[I have made a number of assumptions about your model; so I hope this suggestion makes sense.]