gpt4 book ai didi

sqlite - 无关记录的删除命令的外键不匹配

转载 作者:行者123 更新时间:2023-12-03 16:05:06 25 4
gpt4 key购买 nike

我定义了以下 5 个表,其中一些记录插入到第 4 个中。这是使用启用外键约束的 sqlite 3.7.1.7。

create table if not exists subject (id varchar(50) primary key,desc varchar(100));
insert into subject (id,desc) values ("subject1","test subject");

create table if not exists subjectlevel (id_subject_id varchar(50) references subject(id) on delete cascade, id integer not null, desc varchar(100) not null, questmcmaxselections integer not null, primary key (id_subject_id,id));

insert into subjectlevel (id_subject_id,id,desc,questmcmaxselections) values ("subject1",1,"test subject1 level 1",4);

insert into subjectlevel (id_subject_id,id,desc,questmcmaxselections) values ("subject1",2,"test subject1 level 2",4);

create table if not exists questmc (id integer primary key, text varchar(300) not null, includeallanswers int not null, subject_id varchar(50), subjectlevel_id integer, foreign key (subject_id, subjectlevel_id) references subjectlevel (id_subject_id,id) on delete cascade);

insert into questmc (text,includeallanswers,subject_id,subjectlevel_id) values ("this is a _ question", 1, "subject1",1);

create table if not exists questmcselection (id integer primary key, text varchar(100) not null, subject_id varchar(50), subjectlevel_id integer, foreign key (subject_id, subjectlevel_id) references subjectlevel (id_subject_id,id) on delete cascade);

insert into questmcselection (text,subject_id,subjectlevel_id) values ("this is a solution","subject1",1);

create table if not exists questmc_questmcselection(id integer primary key, answer integer not null, questmc_id integer, questmcselection_id integer, subject_id varchar(50), subjectlevel_id integer, foreign key (questmc_id) references questmc(id) on delete cascade, foreign key (questmcselection_id) references questmcselection (id) on delete cascade, foreign key (subject_id,subjectlevel_id) references questmc (subject_id,subjectlevel_id) on delete cascade, foreign key (subject_id,subjectlevel_id) references questmcselection (subject_id,subjectlevel_id));

如果我尝试删除主题级别表中的第二条记录,只要定义了表 questmc_questmcselection,就会出现外键不匹配错误。
sqlite> delete from subjectlevel where id=2;
Error: foreign key mismatch - "questmc_questmcselection" referencing "questmcselection"

questmc、questmcselection 和 questmc_questmcselection 没有相关的现有记录可以阻止此删除。知道为什么会发生此错误吗?

最佳答案

此错误与此特定 subjectlevel 无关记录。

您的问题是您的表缺少 the required indexes .
这之前没有报告,因为 DELETE语句是需要 SQLite 检查数据库模式一致性的第一个命令。

关于sqlite - 无关记录的删除命令的外键不匹配,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17013339/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com