Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLite - Foreign key referencing rowid alias. Legal or not?

In the SQLite documentation it says:

The parent key of a foreign key constraint is not allowed to use the rowid. The parent key must used named columns only.

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

But does that apply to an alias of the rowid? For example in SQLite if you have a INTEGER PRIMARY KEY column then that column is essentially an alias of the rowid:

With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key".

(Exception omitted; not relevant here)

There is a similar question here: sql - Why does referencing a SQLite rowid cause foreign key mismatch? - Stack Overflow

If I take that example and modify it to use the alias (my integer primary key column) it appears to work:

sqlite> CREATE TABLE foo(a INTEGER PRIMARY KEY, name);
sqlite> create table bar(foo_rowid REFERENCES foo(a));
sqlite> INSERT INTO foo VALUES( NULL, "baz" );
sqlite> select * from foo;
a           name
----------  ----------
1           baz
sqlite> INSERT INTO bar (foo_rowid) VALUES(1);
sqlite> select * from bar;
foo_rowid
----------
1
sqlite>

But is it legal to reference an alias of the rowid? Thanks.

like image 590
user2672807 Avatar asked Jun 02 '14 20:06

user2672807


1 Answers

If the internal rowid is not a named column, it might not keep the same values after a VACUUM, which would break the foreign key references.

If the rowid is named, it is guaranteed to keep its values. Using an INTEGER PRIMARY KEY as the parent of a foreign key is allowed, and common.

like image 64
CL. Avatar answered Oct 04 '22 18:10

CL.