错误代码:1822。未能添加外键约束。引用的表“instructor”中缺少约束“student_ibfk_2”的索引

Question

create database stmikglobal;
use stmikglobal;

create table depart(
    dept_name varchar(20) primary key,
    building varchar(20),
    budget float(15,2)
);

create table instructor(
    instruc_id int not null auto_increment primary key,
    ka_dept_name varchar(20),
    ins_name varchar(30) not null,
    salary int,
    foreign key (ka_dept_name) references depart (dept_name)
    );
    
    
    
create table student(
    id_stu int not null auto_increment primary key,
    ka_dept_name varchar (20),
    ka_ins_name varchar(30),
    full_name varchar(35),
    total_credits int,
    foreign key (ka_dept_name) references depart(dept_name),
    foreign key (ka_ins_name) references instructor(ins_name)
    );

为什么我想在可执行表部分有问题？

Answer 1

您应该通过instructor表的主键(instruction_id)来引用该表，而不是通过其他列（这里使用的是ins_name)。因此更改列名，数据类型和外键声明，如下所示:

create table student(
    id_stu int not null auto_increment primary key,
    ka_dept_name varchar (20),
    ka_instruc_id int,
    full_name varchar(35),
    total_credits int,
    foreign key (ka_dept_name) references depart(dept_name),
    foreign key (ka_instruc_id) references instructor(instruc_id)
);