Foreign key reference in MySQL, using similar referenced column, not woking

Question

Here are two codes

CREATE TABLE tab1 (
   col1 int,
   col2 int,
   PRIMARY KEY(col1, col2)
);

CREATE TABLE tab2 (
   col1 int,
   FOREIGN KEY (col1) REFERENCES tab1(col1)
);

CREATE TABLE tab1 (
   col1 int,
   col2 int,
   PRIMARY KEY(col1, col2)
);

CREATE TABLE tab2 (
   col1 int,
   FOREIGN KEY (col1) REFERENCES tab1(col2) <- difference point (col2 used instead of col1)
);

The first code works but the second doesn't work. There error is as follow:

SQL Error [1822] [HY000]: Failed to add the foreign key constraint. Missing index for constraint 'tab2_ibfk_1' in the referenced table 'tab1'.

But why is it so??

BTW I am clearing the database before executing the second code. So it is not due to some duplicate tables present.

And also this answer says, foreign key cannot reference a column which can contain duplicate values. But in my case, if (1, 1) and (1, 2) are inserted in tab1, then when col1 in referenced from tab2, col1 does contain duplicate entries.

I am using Ubuntu 20.04.

This is an easily found faq. Please before considering posting read the manual & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect your research. See [ask], [Help] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. — philipxy, Sep 19 '21 at 08:11

score 2 · Accepted Answer · answered Sep 19 '21 at 00:01

Standard SQL requires that you define foreign keys to reference the whole primary key or unique key of the parent table. If that primary key has two columns, then your foreign key must also have both columns. So you should not try to do either of the cases you show.

InnoDB allows a deviation from the standard rule: it allows a foreign key to reference a subset of the columns of the primary key in the parent table, as long as the subset are the leftmost columns. The key in the parent table doesn't even need to be a primary or unique key. It can be just a plain secondary index.

But this creates some strange edge cases that cause trouble. If you can create a row in the child table that references more than one row in the parent table, then what happens if one of the rows in the parent table is deleted? Is that a violation of the foreign key? Should it be allowed? Is it only an error if you delete a row in the parent table that is the only parent of that child row? Which behavior is correct?

I strongly recommend you don't travel down that path. Always make sure to define a foreign key that references only a primary or unique key in the parent table, and always define the foreign key with the same set of columns as the key it references. That way, the child row is guaranteed to reference exactly one row in the parent table, and all the weird questions vanish.

Foreign key reference in MySQL, using similar referenced column, not woking

1 Answers1