什么是导致外键不匹配错误？ [英] What is causing Foreign Key Mismatch error?

问题描述

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

  CREATE TABLE如果不存在患者
（PatientId INTEGER PRIMARY KEY AUTOINCREMENT）; 
 
 CREATE TABLE IF NOT EXISTS事件
（
 PatientId INTEGER REFERENCES Patient（PatientId），
 DateTime TEXT，
 EventTypeCode TEXT，
 PRIMARY KEY（PatientId，DateTime，EventTypeCode）
）; 
 
如果不存在，则创建表
（
 PatientId INTEGER REFERENCES Patient（PatientId），
 DateTime TEXT REFERENCES Event（DateTime），
 EventTypeCode TEXT REFERENCES事件（EventTypeCode），
值REAL，
 PRIMARY KEY（PatientId，DateTime，EventTypeCode）
）; 
  

我插入ID为1的患者

然后我运行：

  INSERT INTO事件（PatientId，DateTime，EventTypeCode）VALUES（1，'2011-01-23 19:26:59'，'R'）
  

然后我运行：

  INSERT INTO Reading（PatientId，DateTime，EventTypeCode，Value）VALUES ，'2011-01-23 19:26:59'，'R'，7.9）
  

它给我一个外键不匹配。在所有情况下，患者ID为1，datetime和类型代码在第2次和第3次查询中匹配。我不明白什么是不匹配，但我有一个新的实际上定义外键，我不知道我做错了什么。

解决方案

我不熟悉SQLite，但有一点谷歌调整了这个。文档说

如果数据库模式包含
外键错误，需要
查看多个表
定义来识别，那么在创建
表时，不会检测到那些
错误。相反，这样的
错误阻止应用程序从
准备SQL语句修改
的子内容或父
表的方式使用外部
键。当内容
更改时报告的错误是DML错误，当模式更改时报告错误

是DDL​​错误。因此，换句话说，
配置错误的外键约束
需要同时查看子
和parent是DML错误。 外键的英文
语言错误消息
DML错误通常是外键
不匹配，但也可以是no such
tableif父表不存在
。外键DML错误可能是
如果出现以下情况则报告
：

• 父表不存在， li>
  
• 在外键约束中命名的父键列不存在，
  或


• 键约束不是父表的
  主键，
  不受唯一约束限制
  使用
  中指定的排序序列CREATE TABLE或


• 子表引用父键的主键，不带
  指定主键列，
  
  中的主键列数父键不匹配
  子键列的数量。

我怀疑您可能会遇到

此外，虽然其他数据库可能支持使用非唯一索引作为外键引用（

1. Reading.PatientId 引用 Event.PatientId ，以便 Event 中的完整复合键可由 Reading ，
   
2. 向事件添加 EventId 并使用它作为 Reading 表中的外键（这样您只有 EventId 和 Value 在下读取，您可以得到 PatientId，DateTime，EventTypeCode Event ）。

我建议＃2， 事件中 PatientId，DateTime 和 EventTypeCode 和阅读。

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

I insert a Patient with Id #1

then I run:

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

which works

then I run:

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

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.

解决方案

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

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:

• The parent table does not exist, or
• The parent key columns named in the foreign key constraint do not exist, or
• The parent key columns named in the foreign key constraint are not the primary key of the parent table and are not subject to a unique constraint using collating sequence specified in the CREATE TABLE, or
• The child table references the primary key of the parent without specifying the primary key columns and the number of primary key columns in the parent do not match the number of child key columns.

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

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

这篇关于什么是导致外键不匹配错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！