SQLite is a foreign key that references a rowid alias. Legal or not?

Question

The SQLite documentation says:

The foreign key restriction parent key is not allowed to use a ROWID. The parent key must use only named columns.

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

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

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

(Exception omitted, does not matter here)

There is a similar question here: sql - Why does a SQLite rowid link cause a foreign key mismatch? - stack overflow

If I take this example and modify it to use an alias (my integer primary key column), it works:

 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 right to refer to the rowid alias? Thanks.

Answer 1

If the internal rowid is not a named column, it may not retain the same values ​​after VACUUM , which violates foreign key references.

If the rowid name is specified, it will retain its values. Using the INTEGER PRIMARY KEY as a parent of a foreign key is permitted and common.