How to set foreign key on two tables in MySQL?

Question

I am struggling in resolving a db design for MySQL 5.7 to solve the following scenario:

There are category dimensions that change each day, I store them in category_entities. They belong to a category which relates to a merchant and his products:

merchants

merchant_id | merchant
1 | amazon
2 | ebay
PK: merchant_id

products

prod_id | merchant_id | product
1 | 1 | jumper big
2 | 1 | hat red
3 | 2 | shoe black
3 | 1 | shoe black2 
PK: merchant_id, prod_id

categories

cat_id | merchant_id | category
1 | 1 | jumpers in green 
2 | 1 | hats for woman
3 | 1 | shoes
4 | 2 | jumpers 
5 | 2 | hats for children
6 | 2 | shoes
PK: cat_id

category_entities (30M rows)

cat_id | prod_id | characteristica | date
1 | 1 | 23 | 2021年01月07日
2 | 2 | 22 | 2021年01月07日
1 | 2 | 22 | 2021年01月08日
2 | 3 | 01 | 2021年01月08日
3 | 1 | 22 | 2021年01月08日
4 | 2 | 01 | 2021年01月08日
PK: cat_id, prod_id, date

So the category table defines what categories exists and the category_entities talbe shows the value for each day

How can I set a foreign key on the tables (categories, category_entities) on table products using prod_id, merchant_id. Those fields are the primary key of product.

I as reading about polymorphic assosiations but I am not sure this is one and how to solve this.

• Lennart - Slava Ukraini: If a product is identified by prod_id, merchant_id you need that information in any dependent table, i.e. category_entities. However, your sample data indicates that prod_id alone would be sufficient, is the primary key in product correct? Would a new relation product_merchant solve your issue?
• merlin: The sample data was missing a value, edited question. same prod_id other merchant. The table categories defines which categories exist per merchant while the category_entities logs which characteristica apear each day in those categories per product. My reasoning was that adding merchant_id to category_entities it would be duplicate data as it is already defined in categories. What happens if a product gets removed? I want to make sure that relations are clear not entities and categories exists without coresponding products/categories. That's why I am trying to solve it with fk.
• Lennart - Slava Ukraini: Can a product exist without a merchant?
• merlin: No, thats impossible. The product table represents products found on e.g. amazon, ebay.
• Lennart - Slava Ukraini: No product I am familiar with allows an FK towards a partial key, so that means you will need merchant_id in category_entities

Answer 1

I'll add a partial answer since it is too much information to stuff in comments. I'm not saying that it is the best solution, but unless you want to remodel, it is a possability. Given the facts you presented you can extend category_entities as:

CREATE TABLE category_entities
( cat_id int not null primary key
, prod_id int not null
, merchant_id int not null
, ...
 constraint ... foreign key (prod_id, merchant_id)
 references products (prod_id, merchant_id)
, ...
);

Now, you may have inconsistent merchant_id information between categories and category_entities. You can prevent this by adding a unique constraint in categories:

ALTER TABLE categories ADD CONSTRAINT ak1_categories
 UNIQUE (cat_id, merchant_id);

Now you can reference this constraint in category_entities as:

ALTER TABLE category_entities ADD CONSTRAINT fk1_categories
 FOREIGN KEY (cat_id, merchant_id)
 REFERENCES categories (cat_id, merchant_id);

This will guarantee consistency between category and category_entities, but is a bit ugly since ak1_categories is a reducible key.

I've heard rumours that there exists DBMS that allow CHECK constraints with sub-queries, but I never used one. Beside MySQL 5.7 doesn't care about check constraints so that is not an option for you.

Another alternative is to add before triggers for validation of merchant_id. They are however procedural by nature so they do not tell you anything about the current situation. All you know is that no invalid data passed them as long as they were active.

As mentioned this is not a complete answer but presents some ideas that do not fit in a comment.

• merlin: Great! That seems to have solved the problem. I have a on delete cascade option set and this worked perfectly. Please allow me one more question: I have a PK set on cat_id in category. Now I added the unique index cat_id, merchant_id. Would it not be better to extend PK to merchant_id instead of adding another unique index?
• Lennart - Slava Ukraini: If you extend the primary key that will allow the same cat_id for multiple merchant_id.
• merlin: True! Thank you for the follow up.
• Lennart - Slava Ukraini: Also, the constraint is implemented via a unique index, but the index is not part of the logical model where constraints reside. Not sure about MYSQL but for most DBMS you cant reference a unique index, you need the constraint.