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?
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.
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.