为什么引用 SQLite rowid 会导致外键不匹配?

2022-01-20 00:00:00 sql database foreign-keys sqlite relational-database 
SQLite version 3.7.9 2011-11-01 00:52:41
sqlite> PRAGMA foreign_keys = 1;
sqlite> CREATE TABLE foo(name);
sqlite> CREATE TABLE bar(foo_rowid REFERENCES foo(rowid));
sqlite> INSERT INTO foo VALUES('baz');
sqlite> SELECT rowid, name FROM foo;
1|baz
sqlite> INSERT INTO bar (foo_rowid) VALUES (1);
Error: foreign key mismatch

为什么会出现这个错误?这是一个 DML 错误，但我不知道出了什么问题，因为:

Why does this error occur? It is a DML error, but I don't know what's wrong because:

  • foo 存在.
  • foo.rowid 存在.
  • foo.rowid 是 foo 的主键，因此被限制为唯一性.
  • bar.foo_rowid 是一列，这与 foo.rowid 是一列的事实相匹配.
  • foo exists.
  • foo.rowid exists.
  • foo.rowid is the primary key of foo and therefore constrained to uniqueness.
  • bar.foo_rowid is one column, which matches the fact that foo.rowid is one column.

推荐答案

SQLite 文档对外键非常清楚:

SQLite documentation is quite clear on foreign keys:

The parent key must be a named column or columns in the parent table, not the rowid.

(请参阅此处.)

您不能为此使用 rowid，因此只需为表定义您自己的自动递增主键.

You can't use rowid for this, so just define your own auto incrementing primary key for the table.

相关文章