Hibernate ManyToMany Relation with common foreign key

Question

I try to implement the same problem as mentioned here some years ago: Hibernate many to many with a composite key with one field shared on either side of the relationship

I have a Menu and an Item class and want to implement a unidirectional relation that a menu saves all the items it contains.

Menu and Item both have composite keys out of the merchant_id foreign key and an auto incremental itemId/menuId. (EER Diagram Image)

Because Hibernate can not retrieve the auto generated Id when I declare a composite key and the Id is unique in the system, I save the entities without an extra embeddedId PKClass:

 @Entity
 @Table(name="ITEM")
 public class Item extends AbstractTimestampEntity{
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="itemId", nullable=false)
    private long itemId;
    @ManyToOne
    @JoinColumn(name="merchantId", nullable=false)
    private Merchant merchant;
    @Column(name="name", length=45)
    private String name;
    @Column(name="description" , length=200)
    private String description;
    @Column(name="price")
    private double price;

    public Item(){} // getters & setters

---

@Entity
@Table(name="MENU")
public class Menu {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="menuId", nullable=false)
    private long menuId;
    @ManyToOne
    @JoinColumn(name="merchantId", nullable=false)
    private Merchant merchant;
    @Column(name="name", length=45)
    private String name;
    @ManyToMany
    @JoinTable(name="MENU_ITEM", joinColumns = {
        @JoinColumn(name="menuId", nullable=false, updatable=false)},
        //@JoinColumn(name="merchant.merchantId", nullable=false, updatable=false)},
        inverseJoinColumns = { @JoinColumn(name="itemId", nullable=false, updatable=false)})
        //                     @JoinColumn(name="merchantId", nullable=false, updatable=false)})
    private List<Item> items = new ArrayList<Item>(); // constructor, getters & setters

As you can see from the commented code, here is the point were my question comes in. How do I map the entities now the best without modifying my normalized database table? (They need to have the same merchant to be validated in the database)

Answer 1

I suspect you may want something like this:

CREATE TABLE `MENU_ITEM` (
    `merchant_id` INT NOT NULL,
    `menu_id` INT NOT NULL,
    `item_id` INT NOT NULL,

    PRIMARY KEY (`merchant_id`, `menu_id`, `item_id`),

    INDEX `ix_menuitem_item` (`item_id`, `merchant_id`),
    INDEX `ix_menuitem_menu` (`menu_id`, `merchant_id`),
    INDEX `ix_menuitem_merchant` (`merchant_id`),

    CONSTRAINT `fk_menuitem_merchant`
        FOREIGN KEY (`merchant_id`)
        REFERENCES `merchant` (`id`),

    CONSTRAINT `fk_menuitem_menu`
        FOREIGN KEY (`menu_id`, `merchant_id`)
        REFERENCES `menu` (`id`, `merchant_id`),

    CONSTRAINT `fk_menuitem_item`
        FOREIGN KEY (`item_id`, `merchant_id`)
        REFERENCES `item` (`id`, `merchant_id`)
)

but, unfortunately, this is impossible.

One column may be used in at most 1 foreign key, in this case MENU_ITEM.merchant_id is used 3 times (at most 2, removing fk_menuitem_merchant).

So, you may want something equivalent:

CREATE TABLE `MENU_ITEM` (
    `merchant_id` INT NOT NULL,
    `menu_id` INT NOT NULL,
    `menu_merchant_id` INT NOT NULL,
    `item_id` INT NOT NULL,
    `item_merchant_id` INT NOT NULL,

    PRIMARY KEY (`merchant_id`, `menu_id`, `item_id`),

    INDEX `ix_menuitem_item` (`item_id`, `merchant_id`),
    INDEX `ix_menuitem_menu` (`menu_id`, `merchant_id`),
    INDEX `ix_menuitem_merchant` (`merchant_id`),

    CONSTRAINT `fk_menuitem_merchant`
        FOREIGN KEY (`merchant_id`)
        REFERENCES `merchant` (`id`),

    CONSTRAINT `fk_menuitem_menu`
        FOREIGN KEY (`menu_id`, `menu_merchant_id`)
        REFERENCES `menu` (`id`, `merchant_id`),

    CONSTRAINT `fk_menuitem_item`
        FOREIGN KEY (`item_id`, `item_merchant_id`)
        REFERENCES `item` (`id`, `merchant_id`),

    CHECK (`merchant_id` = `menu_merchant_id`),
    CHECK (`merchant_id` = `item_merchant_id`)
)

but, unfortunately again, MySQL doesn't support CHECK.

As you can see, this is not a ORM problem.

So, you have two choices:

1. implement some trigger to simulate CHECK (see here)

2. let the application do the check:

   @Entity
   public class Menu
   {
       protected class ItemList extends AbstractList<Item>
       {
           protected ArrayList<Item> list;
   
           public ItemList()
           {
               super();
               list = new ArrayList<>();
           }
   
           public ItemList(Collection<? extends Item> c)
           {
               super();
               list = new ArrayList<>(c.size());
               addAll(c);
           }
   
           @Override
           public boolean add(Item item)
           {
               if(!Objects.equals(merchant, item.merchant))
               {
                   throw new IllegalArgumentException();
                   // or return false;
               }
   
               return list.add(item);
           }
   
           @Override
           public Item get(int index)
           {
               return list.get(index);
           }
   
           @Override
           public int size()
           {
               return list.size();
           }
       }
   
       @Id
       @GeneratedValue(strategy = GenerationType.IDENTITY)
       @Column(name = "id", nullable = false)
       protected long id;
   
       @ManyToOne
       @JoinColumn(name = "merchant_id", nullable = false)
       protected Merchant merchant;
   
       @Column(name = "name", length = 45)
       protected String name;
   
       @ManyToMany
       @JoinTable(name = "MENU_ITEM",
           joinColumns = @JoinColumn(name = "menu_id"),
           inverseJoinColumns = @JoinColumn(name = "item_id"))
       protected List<Item> items = new ItemList();
   
       public List<Item> getItems()
       {
           return items;
       }
   
       public void setItems(List<Item> items)
       {
           this.items = new ItemList(items);
       }
   }