错误代码:1822 当数据类型匹配时，使用复合键

2022-01-20 00:00:00 foreign-keys mysql mysql-workbench

得到一个

错误代码:1822.添加外键约束失败.失踪引用表中约束subject_ibfk_1"的索引'注册'

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'subject_ibfk_1' in the referenced table 'enrolment'

尝试创建 subject 表时.问题是，上一张表student 上不会出现错误.数据类型相同，主键已定义.

when attempting to create the subject table. The problem is, the error does not occur on the previous table student. The data types are the same, and the primary keys are defined.

enrolment 和 grade 表都会出现此错误.

This error occurs for both the enrolment and grade tables.

create table enrolment(
    stud_id char(9) not null,
    subj_code char(8) not null,
    semester tinyint unsigned not null,
    year smallint unsigned not null,
    comment text,

    primary key (stud_id, subj_code, semester, year)
);

create table grade(
    stud_id char(9) not null,
    subj_code char(8) not null,
    semester tinyint unsigned not null,
    year smallint unsigned not null,
    grade tinyint unsigned,

    primary key (stud_id, subj_code, semester, year)
);

create table student(
    stud_id char(9) not null,
    stud_name char(30),
    stud_phone char(12),
    stud_date_of_birth date,
    stud_city char(26),
    stud_address char(30),
    stud_postcode char(4),

    primary key (stud_id),

    foreign key (stud_id)
        references grade(stud_id),
    foreign key (stud_id)
        references enrolment(stud_id)
);

create table subject(
    subj_code char(8) not null,
    subj_title char(40),

    primary key (subj_code),

    foreign key (subj_code)
        references enrolment(subj_code),

    foreign key (subj_code)
        references grade(subj_code)
);

推荐答案

问题是由于外键 subj_code 是 多列 的一部分em> 引用表中的主键(PK)enrolment:

The problem is due to the fact that the foreign key, subj_code, is part of a multi-column primary key (PK) in the referenced table enrolment:

primary key (stud_id, subj_code, semester, year)

此列 (subj_code) 不是最左边的列.

表student不存在这个问题，因为它的外键列stud_id是被引用表中PK的最左列.

Table student does not have this problem because its foreign key column stud_id is the leftmost column of the PK in the referenced table.

要解决此问题，您可以为引用的列创建一个新索引:

To resolve this you can create a new index for the referened column:

ALTER TABLE enrolment ADD INDEX subj_code_idx (subj_code);

注意:您必须对另一个外键中的引用表 grade 执行相同操作.

Note: You have to do the same for referenced table grade in the other foreign key.

此处演示

相关文章