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)

can you show `MENU_ITEM` foreign keys? – Michele Mariotti Jan 15 '17 at 11:52 — Michele Mariotti, Jan 15 '17 at 11:52

score 0 · Accepted Answer · edited May 23 '17 at 12:16

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:

implement some trigger to simulate CHECK (see here)

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);
    }
}

Thank you! yes I was thinking about doing the check only in the application layer. However I was not sure if there is a better way which does not change the normalized DB-Design. — kaikun, Jan 15 '17 at 13:37

Hibernate ManyToMany Relation with common foreign key

1 Answers1