CODEWITHSUNDEEP
mysqlforeign-keys
STEM gene
STEM gene

Reputation: 1

How to fix an index error when creating a foreign constraint

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
 );

Upvotes: 0

Views: 439

Answers (2)

MattOverF.
MattOverF.

Reputation: 69

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

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

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

Upvotes: 1

Related Questions