为什么不能从表中删除引用另一个 [英] Why cannot delete from a table with reference to another

问题描述

我已经寻找答案，但也许我错过了一些东西。
我有2个表，见下文。第一个表中的实体从第二个表引用，但是当我尝试从第二个表中删除时，我得到错误：外键不匹配。有触发器（这里不显示），但它们与 DELETE 或 cmdauth 无关。我不明白为什么不能删除行？

I have looked for answers but maybe I miss something. I have 2 tables see below. The entities from the first table is referenced from the second one, but when I try to delete from the second I get Error: foreign key mismatch. There are triggers (not show here), but they have nothing to do with DELETE or cmdauth. I do not understand why cannot remove row?

CREATE TABLE app (name TEXT, script TEXT, PRIMARY KEY(name));
CREATE TABLE env (name TEXT, PRIMARY KEY(name));
CREATE TABLE role (name TEXT, command TEXT, PRIMARY KEY(name,command));

CREATE TABLE cmdauth (groupname TEXT, rolename TEXT, appname TEXT, envname TEXT, FOREIGN KEY (appname) REFERENCES app(name), FOREIGN KEY (rolename) REFERENCES role(name), FOREIGN KEY (envname) REFERENCES env(name), PRIMARY KEY (groupname,rolename,appname,envname));

sqlite> select * from cmdauth where appname='app1' and groupname='admin' and envname='test' and rolename='restarter';
admin|restarter|app1|test
sqlite> delete from cmdauth where appname='app1' and groupname='admin' and envname='test' and rolename='restarter';
Error: foreign key mismatch

感谢您的帮助！

推荐答案

文档< a>说：

The documentation says:

通常，外键约束的父键是父表的主键。如果它们不是主键，则父键列必须共同受制于UNIQUE约束或具有UNIQUE索引。

Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index.

对 role（name）的引用不满足此要求。

This requirement is not met for the reference to role(name).

这篇关于为什么不能从表中删除引用另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！