是什么导致外键不匹配错误?

2021-12-08 00:00:00 sql constraints foreign-keys sqlite

我有一个结构如下的sqlite数据库:

I have an sqlite database structured as follows:

CREATE TABLE IF NOT EXISTS Patient 
( PatientId INTEGER PRIMARY KEY AUTOINCREMENT );

CREATE TABLE IF NOT EXISTS Event 
( 
PatientId INTEGER REFERENCES Patient( PatientId ),
DateTime TEXT,
EventTypeCode TEXT,
PRIMARY KEY( PatientId, DateTime, EventTypeCode )
);

CREATE TABLE IF NOT EXISTS Reading 
( 
PatientId INTEGER REFERENCES Patient( PatientId ),
DateTime TEXT REFERENCES Event (DateTime),
EventTypeCode TEXT REFERENCES Event (EventTypeCode),
Value REAL,
PRIMARY KEY( PatientId, DateTime, EventTypeCode )
);

我插入一个 ID 为 #1 的患者

I insert a Patient with Id #1

然后我跑:

INSERT INTO Event (PatientId, DateTime, EventTypeCode) VALUES (1, '2011-01-23 19:26:59', 'R')

哪个有效

然后我跑:

INSERT INTO Reading (PatientId, DateTime, EventTypeCode, Value) VALUES (1, '2011-01-23 19:26:59', 'R', 7.9)

它给了我一个外键不匹配.患者 ID 在所有情况下都是1"，并且日期时间和类型代码在第二次和第三次查询中匹配.我不明白什么是不匹配，但我对实际定义外键有点陌生，我不知道我做错了什么.

and it gives me a foreign key mismatch. Patient Id is '1' in all cases, and the datetime and typecodes match in the 2nd and 3rd queries. I do not understand what is mismatching, but I'm a bit new to actually defining foreign keys and i do not know what I am doing wrong.

推荐答案

我对 SQLite 不熟悉，但对 Google 有点了解 发现了这个.文档说

I'm not familiar with SQLite but a little Google'ing turned up this. The documentation says

如果数据库架构包含需要的外键错误看着不止一张桌子定义来识别，然后那些错误不会被检测到表被创建.相反，这样的错误阻止应用程序准备修改的 SQL 语句孩子或父母的内容表以使用外国的方式键.内容错误时报告的错误更改为DML 错误"和错误当架构改变时报告是DDL​​ 错误".所以，换句话说，错误配置的外键约束需要同时看着孩子和 parent 是 DML 错误.英语外键的语言错误信息DML 错误通常是外键不匹配" 但也可以是没有这样的table" 如果父表没有存在.外键 DML 错误可能是是在以下情况下报告:

If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created. Instead, such errors prevent the application from preparing SQL statements that modify the content of the child or parent tables in ways that use the foreign keys. Errors reported when content is changed are "DML errors" and errors reported when the schema is changed are "DDL errors". So, in other words, misconfigured foreign key constraints that require looking at both the child and parent are DML errors. The English language error message for foreign key DML errors is usually "foreign key mismatch" but can also be "no such table" if the parent table does not exist. Foreign key DML errors are may be reported if:

  • 父表不存在，或者
  • 外键约束中命名的父键列不存在，或
  • 外键约束中命名的父键列不是父表的主键和不受唯一约束使用中指定的整理顺序创建表，或
  • 子表没有引用父表的主键指定主键列和中的主键列数父母的人数不匹配子键列.

我怀疑您可能会遇到该列表中的第 3 名.

I suspect you might be running into #3 in that list.

此外，虽然其他数据库可能支持使用非唯一索引作为外键引用，(在这里查看答案)，在我看来这是一个糟糕的设计选择.我会重组，以便

Also, while other DBs might support using a non-unique index as a foreign key reference, (see answers here), it's a bad design choice in my opinion. I would restructure so that either

  1. Reading.PatientId 引用 Event.PatientId，以便 Reading 引用 Event 的完整组合键或者，
  2. 向 Event 表添加一个 EventId 自动递增主键，并将其用作 Reading 表中的外键(因此你只有 EventId 和 Value 在 Reading 下，你可以从 PatientId, DateTime, EventTypeCode代码>事件).
  1. Reading.PatientId references Event.PatientId so that the complete composite key from Event is referenced by Reading or,
  2. Add an EventId auto-increment, primary key to the Event table and use that as the foreign key in the Reading table (so that you only have EventId and Value under Reading and you can get the PatientId, DateTime, EventTypeCode out of Event).

我建议使用 #2，这样您就可以避免 Event 和 中 PatientId、DateTime 和 EventTypeCode 的冗余阅读.

I'd suggest #2 so that you can avoid the redundancy of PatientId, DateTime and EventTypeCode in both Event and Reading.

相关文章