错误代码：1822。无法添加外键约束。引用表"教师"中缺少约束"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表的主键(instruc_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)
);