mysql外键多次引用

Question

1822报错，不知道咋引用外键，我看英文说是引用的表里丢失了这个，但我表里有。我建立了一个八表的联动，无内容，仅有列名跟主键非空，想知道怎么多次引用一个表里的列作为外键。

Answer 1

引自免费微信小程序：皆我百晓生

看起来你在尝试在MySQL数据库中设置外键约束，但遇到了错误1822，这通常意味着在引用表中缺少相应的索引。为了解决这个问题，你需要确保每个被引用的列都有一个索引。在这个例子中，错误提示说在teacher表中缺少关于tno的索引。让我们按照正确的顺序重新创建表并添加缺失的索引，然后再次尝试添加外键约束。

首先，我们创建带有索引的表：

CREATE TABLE department (
    deptno CHAR(4) PRIMARY KEY
);

CREATE TABLE class (
    classno CHAR(3) PRIMARY KEY,
    deptno CHAR(4),
    INDEX idx_deptno (deptno)
);

CREATE TABLE student (
    sno CHAR(10) PRIMARY KEY,
    classno CHAR(3),
    INDEX idx_classno (classno)
);

CREATE TABLE course (
    cno CHAR(3) PRIMARY KEY,
    deptno CHAR(4),
    INDEX idx_deptno_course (deptno)
);

CREATE TABLE teacher (
    tno CHAR(4) PRIMARY KEY,
    deptno CHAR(4),
    INDEX idx_deptno_teacher (deptno)
);

CREATE TABLE sc (
    cno CHAR(3) NOT NULL,
    sno CHAR(10) NOT NULL,
    degree DECIMAL(4, 1),
    cterm TINYINT NOT NULL,
    PRIMARY KEY (cno, sno, cterm),
    INDEX idx_cno (cno),
    INDEX idx_sno (sno),
    INDEX idx_cterm (cterm)
);

CREATE TABLE teaching (
    tno CHAR(4) NOT NULL,
    cno CHAR(3) NOT NULL,
    cterm TINYINT NOT NULL,
    PRIMARY KEY (tno, cno, cterm),
    INDEX idx_tno (tno),
    INDEX idx_cno_teaching (cno),
    INDEX idx_cterm_teaching (cterm)
);

接着，我们可以尝试添加外键约束：

ALTER TABLE class ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES department (deptno);

ALTER TABLE student ADD CONSTRAINT fk_classno FOREIGN KEY (classno) REFERENCES class (classno);

ALTER TABLE course ADD CONSTRAINT fk_deptno_course FOREIGN KEY (deptno) REFERENCES department (deptno);

ALTER TABLE sc ADD CONSTRAINT fk_cno FOREIGN KEY (cno) REFERENCES course (cno);

ALTER TABLE sc ADD CONSTRAINT fk_sno FOREIGN KEY (sno) REFERENCES student (sno);

ALTER TABLE sc ADD CONSTRAINT fk_cterm FOREIGN KEY (cterm) REFERENCES course (cterm);

ALTER TABLE teaching ADD CONSTRAINT fk_tno FOREIGN KEY (tno) REFERENCES teacher (tno);

ALTER TABLE teaching ADD CONSTRAINT fk_cno FOREIGN KEY (cno) REFERENCES course (cno);

ALTER TABLE teaching ADD CONSTRAINT fk_cterm FOREIGN KEY (cterm) REFERENCES course (cterm);

这样应该就可以成功地添加所有外键约束，而不会收到错误1822。如果你仍然遇到问题，请检查是否有其他潜在的索引问题或数据类型不匹配的情况。