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

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?

CodePudding user response：

CREATE TABLE parent (
  aTableId INT PRIMARY KEY,
  type VARCHAR(10) NOT NULL,
  category VARCHAR(10) NOT NULL,
  UNIQUE KEY (type, category)
);

CREATE TABLE child (
  bTableId INT PRIMARY KEY,
  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.