Why can't i add a foreign key formed of two columns?

Question

Why can't I add the foreign key group in note?

I get Error Code: 1215. Cannot add foreign key constraint.

create table student(
    id_student int auto_increment not null,
    prenume varchar(255) not null,
    nume varchar(255) not null,
    constraint id_student_pk_STUDENT primary key(id_student));

create table materie(
    id_materie int auto_increment not null,
    nume_materie varchar(255) not null,
    constraint id_materie_pk_MATERIE primary key(id_materie));

create table inscris(
    id_student int not null,
    id_materie int not null,
    constraint id_student_fk_INSCRIS foreign key(id_student) references student(id_student),
    constraint id_materie_fk_INSCRIS foreign key(id_materie) references materie(id_materie));

create table note(
    id_student int not null,
    id_materie int not null,
    nota int,
    constraint fk_id_student_materie_NOTE foreign key(id_student, id_materie) references inscris(id_student, id_materie));

This is what show engine innodb status says:

LATEST FOREIGN KEY ERROR\n------------------------\n2017-05-18 19:44:21 0x700007362000 Error in foreign key constraint of table catalog/note:\n foreign key(id_student, id_materie) references inscris(id_student, id_materie)):\nCannot find an index in the referenced table where the\nreferenced columns appear as the first columns, or column types\nin the table and the referenced table do not match for constraint.\nNote that the internal storage type of ENUM and SET changed in\ntables created with >= InnoDB-4.1.12, and such columns in old tables\ncannot be referenced by such columns in new tables.\nPlease refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

Possible duplicate http://stackoverflow.com/questions/953035/multiple-column-foreign-key-in-mysql? — xQbert, May 18 '17 at 16:53
A foreign key must reference a key, you need to add an index in `inscris` that includes both the fields referenced in `note`'s FK. The other FKs work not because they are single column, but because the columns referenced are indexed. — Uueerdo, May 18 '17 at 16:53

score 1 · Accepted Answer · answered May 18 '17 at 16:58

1

A foreign key must reference a key, you need to add an index in inscris that includes both the fields referenced in note's FK. The other FKs work not because they are single column, but because the columns referenced are indexed.

answered May 18 '17 at 16:58

Uueerdo

15,723
1
16
21

score 0 · Answer 2 · answered May 18 '17 at 16:55

0

Maybe this alternative will bypass your problem. Add a third column to your inscris table, and make that third column a primary key. Then refer your note foreign key to that primary key column.

answered May 18 '17 at 16:55

Andrew

585
4
17

Why can't i add a foreign key formed of two columns?

2 Answers2