SQL 什么导致外键不匹配错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5208245/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is causing Foreign Key Mismatch error?
提问by Damon
I have an sqlite database structured as follows:
我有一个结构如下的sqlite数据库：
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
我插入一个 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.
它给了我一个外键不匹配。Patient Id 在所有情况下都是“1”，并且日期时间和类型代码在第 2 次和第 3 次查询中匹配。我不明白什么是不匹配，但我对实际定义外键有点陌生，我不知道我做错了什么。
回答by no.good.at.coding
I'm not familiar with SQLite but a little Google'ing turned up this. The documentation says
我对 SQLite 不熟悉，但有一点谷歌发现了这个。文档说
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.
如果数据库模式包含需要查看多个表定义来识别的外键错误，那么在创建表时不会检测到这些错误。相反，此类错误会阻止应用程序准备以使用外键的方式修改子表或父表内容的 SQL 语句。更改内容时报告的错误为“DML 错误”，更改架构时报告的错误为“DDL 错误”。因此，换句话说，需要查看子级和父级的错误配置的外键约束是 DML 错误。外键 DML 错误的英文错误信息通常是“外键不匹配”但如果父表不存在，也可以是“没有这样的表”。如果出现以下情况，可能会报告外键 DML 错误：
- 父表不存在，或
- 外键约束中命名的父键列不存在，或
- 外键约束中命名的父键列不是父表的主键，并且不受使用 CREATE TABLE 中指定的整理顺序的唯一约束，或
- 子表引用父表的主键，没有指定主键列，父表的主键列数与子键列数不匹配。
I suspect you might be running into #3 in that list.
我怀疑您可能会遇到该列表中的#3。
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
此外，虽然其他数据库可能支持使用非唯一索引作为外键引用（请参阅此处的答案），但在我看来，这是一个糟糕的设计选择。我会重组，以便要么
Reading.PatientIdreferencesEvent.PatientIdso that the complete composite key fromEventis referenced byReadingor,- Add an
EventIdauto-increment, primary key to theEventtable and use that as the foreign key in theReadingtable (so that you only haveEventIdandValueunderReadingand you can get thePatientId, DateTime, EventTypeCodeout ofEvent).
Reading.PatientId引用，Event.PatientId以便完整的组合键 fromEvent被Readingor引用，EventId向Event表中添加一个自动递增的主键，并将其用作表中的外键Reading（这样您就只有EventId和ValueunderReading并且可以从中PatientId, DateTime, EventTypeCode取出Event）。
I'd suggest #2 so that you can avoid the redundancy of PatientId, DateTimeand EventTypeCodein both Eventand Reading.
我建议 #2 这样你就可以避免PatientId, DateTime和EventTypeCode中的Event和冗余Reading。
