[SOLVED] Spring JPA (MariaDB) Is it possible to use multiple non-primary keys as foreign keys?


This Content is from Stack Overflow. Question asked by 남혁준

A parent table:
int aTableId; (key)
String type;
String category;

B child table:
int bTableId; (key)
String title;
String type;
String category;

What I want to do is make the “combination” of type and category existing in table A as a foreign key to the combination of type and category in table B.

in a formula
a.type == b.type && a.category == b.category
must be satisfied to insert into table B.

Let me explain it again

A parent table
1. type : food, category : fruit
2. type: food, category: vegetables
3. type : person, category : head

If such a combination exists in A parent table,

food - fruit
food - vegetables
person - head

The above combination is
It is possible to insert into table B, but

person - fruit
person - vegetables
food - head

This combination is not possible.

How can I achieve what I want?


  type VARCHAR(10) NOT NULL,
  category VARCHAR(10) NOT NULL,
  UNIQUE KEY (type, category)

  title VARCHAR(50) NOT NULL,
  type VARCHAR(10) NOT NULL,
  category VARCHAR(10) NOT NULL,
  FOREIGN KEY (type, category) REFERENCES parent(type, category)

You can define a foreign key that references a secondary candidate key of the parent table. You should make that key a reliable candidate key: it should be unique and NOT NULL, so each reference in the child table is guaranteed to reference only one row in the parent table.

The unique key and the foreign key that references it can have multiple columns. The foreign key must have the same number and types of columns as the candidate key it references.

This Question was asked in StackOverflow by 남혁준 and Answered by Bill Karwin It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?