On Tuesday, 29 October, 2019 12:25, Doug <dougf....@comcast.net> wondered:
>The draft says "Nor may a generated column depend on the ROWID." >If my table uses ROWID by default: >CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS (id+a)); >where id is ROWID by default, is the generated column disallowed because >it has an implied dependency on ROWID? sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS (id+a)); sqlite> insert into foo values (1,1),(2,1),(3,1); sqlite> select * from foo; 1|1|2 2|1|3 3|1|4 sqlite> create table bar (a INTEGER, b AS (_rowid_ + a)); Error: no such column: _rowid_ sqlite> create table bar (a INTEGER, b AS (rowid + a)); Error: no such column: rowid sqlite> create table bar (id INTEGER PRIMARY KEY, a INTEGER, b AS (_rowid_+a)); Error: no such column: _rowid_ sqlite> create table bar (id INTEGER PRIMARY KEY, a INTEGER, b AS (rowid + a)); Error: no such column: rowid sqlite> create table bar (_rowid_ INTEGER PRIMARY KEY, a INTEGER, b as (_rowid_ + a)); sqlite> insert into bar (a) values (1),(1),(1); sqlite> select * from bar; 1|1|2 2|1|3 3|1|4 So a generated column may not depend on the IMPLICITLY named rowid (by whatever magic you want to use), but it may depend on an EXPLICIT rowid (or alias). In other words, only explicitly named columns are permitted. If you EXPLICITY named the rowid alias to be _rowid_ it works without problem. Same rule applies to FOREIGN KEY contraints which may only depend on EXPLICITLY named columns, not the IMPLICIT rowid. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
