pp泉
2021-03-27 13:27
采纳率: 100%
浏览 52

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

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);

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • 沐川 2021-03-27 19:14
    已采纳

    增加两个索引:

    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.

     

    点赞 评论
  • 沐川 2021-03-28 01:39

    给我留个言,我明天来验证

    点赞 评论
  • 有问必答小助手 2021-03-29 11:35

    您好,我是问答小助手,你的问题已经有小伙伴为您解答了问题,您看下是否解决了您的问题,可以追评进行沟通哦~

    如果有您比较满意的答案 / 帮您提供解决思路的答案,可以点击【采纳】按钮,给回答的小伙伴一些鼓励哦~~

    ps:问答VIP仅需29元,即可享受5次/月 有问必答服务,了解详情>>> https://vip.csdn.net/askvip?utm_source=1146287632

    点赞 评论

相关推荐 更多相似问题