求大神看看，mysql主键和外键为什么没有关联

Question

create database studentsdb;

alter database studentsdb character set utf8;

use studentsdb;

 

create table student_info(

student_id char(4) not null primary key,

student_name char(8) not null,

sex char(2),

birthday date,

address varchar(50));

 

create table curriculum(

class_id char(4) not null primary key,

class_name varchar(50),

credit int);

 

create table grade(

student_id char(4),

class_id char(4),

score int);

 

alter table grade add primary key(student_id,class_id);

 

alter table grade add constraint fk_student_id foreign key(student_id) references student_info(student_id) on delete cascade;

 

alter table grade add constraint fk_class_id foreign key(class_id) references curriculum(class_id) on delete cascade;

 

insert into student_info(student_id,student_name)values('1001','张三');

 

insert into curriculum(class_id)values('123');

前面把grade表的student_id和class_id都设为了外键，但是下面插入主键中没有的值为什么还是可以插入？

insert into grade(student_id,class_id,score)values('1002','456',85);

Answer 1

增加两个索引：

alter table grade add key (student_id);
alter table grade add key (class_id);

参考资料：https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html#foreign-key-restrictions

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.