How to fix an index error when creating a foreign constraint

Question

I'm trying to create two relations by MySQL, while I was creating the second relation "t2", I added a foreign constraint to the first relation "t1".

The first table "t1" was been built successfully, but it showed "Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'FK2' in the referenced table 't1'" during creating the second table "t2".

I have checked some issues like mine, some solutions were the referenced attribute in the first table should be unique or primary key. But "Mon" already has been the PK in the first table.

What's wrong in my code?

create table t1
( Num       INT(10)        NOT NULL,
  Mon       VARCHAR(7)        NOT NULL,

  CONSTRAINT PK1 PRIMARY KEY (Num, Mon)
);

CREATE TABLE t2
( Num        INT(10)        NOT NULL,
  Mon       VARCHAR(7)        NOT NULL,
  Totle_Income        Decimal(9,2)   DEFAULT 0,
  CONSTRAINT PK2 PRIMARY KEY (Num, Mon),
  CONSTRAINT FK1 FOREIGN KEY (Num) REFERENCES t1(Num)
                       ON DELETE CASCADE      ON UPDATE CASCADE,
  CONSTRAINT FK2 FOREIGN KEY (Mon) REFERENCES t1(Mon)
                       ON DELETE CASCADE       ON UPDATE CASCADE
 );

You might like to read this checklist for foreign keys which I contributed to: https://stackoverflow.com/a/4673775/20860 — Bill Karwin, Mar 29 '19 at 16:00

score 1 · Answer 1 · answered Mar 29 '19 at 15:59

1

t1.mon is only the second component of the primary key, thus also only the second component of the index. That doesn't usefully index t1.mon, it needed to be the first or only component of the index.

But you likely want to reference the complete key. So the foreign key constraint should include both columns rather than having two constraints for each part of the key.

...
CONSTRAINT FK1
           FOREIGN KEY (Num,
                        Mon)
                       REFERENCES t1
                                  (Num,
                                   Mon)
                       ON DELETE CASCADE
                       ON UPDATE CASCADE,
...

answered Mar 29 '19 at 15:59

sticky bit

36,626
12
31
42

Thanks, it works. But if I want to reference 2 tables, say, "Num" refers to t1 "Num" and "Mon" refers to t0 "Mon" (assume that there is a table "t0"). May I code it "...REFERENCES t1 (Num), t0 (Mon)..."? – STEM gene Mar 29 '19 at 19:36
@STEMgene: No, that'd require indeed two different constraints. – sticky bit Mar 29 '19 at 19:38

score 0 · Answer 2 · answered Mar 29 '19 at 16:09

0

t1.monis not unique and therefore can not be refered as a foreign_key

answered Mar 29 '19 at 16:09

MattOverF.

69
7

How to fix an index error when creating a foreign constraint

2 Answers2