Geek Answers Handbook

Why does a SQLite rowid link cause a foreign key mismatch?

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

Why does this error occur? This is a DML error , but I do not know what is wrong, because:

  • foo exists.
  • foo.rowid exists.
  • foo.rowid is the primary key of foo and is therefore limited by uniqueness.
  • bar.foo_rowid is a single column, which corresponds to the fact that foo.rowid is a single column.
+7
source share
2 answers

The SQLite documentation is pretty clear for foreign keys:

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

(see here .)

You cannot use rowid for this, so just specify your own automatically incrementing primary key for the table.

+13
source

You cannot use rowid if it is not defined in your table, but if you define it as follows:

 CREATE TABLE IF NOT EXISTS Clase( ROWID INTEGER NOT NULL, nombre VARCHAR(50) NOT NULL, PRIMARY KEY(ROWID));

The ROWID column can be used to create external links, and when you insert a record into the table, the ROWID column behaves like an auto-increment field, so it is recommended that there are no auto-increment fields in sqlite.

Note. The ROWID column can be called differently, only it must be of type INTEGER and the primary key of the table.

+2
source

More articles:


All Articles