错误代码:1822当数据类型匹配时，使用复合键 [英] Error Code: 1822 when data types are matching, with composite key

问题描述

获取

错误代码: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'

尝试创建主题表时.问题是，上一个表 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.

enrollment 和 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>引用表 enrollment 中的主键(PK):

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.

> 此处演示

这篇关于错误代码:1822当数据类型匹配时，使用复合键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！