Re: [sqlite] keys
On Friday, 24 August, 2018 17:31, w...@us.net wrote: >"The parent key of a foreign key constraint is not allowed to use the >rowid. The parent key must used named columns only." >Why is this? You should think of this as: >"The parent key of a foreign key constraint is not allowed to use the >rowid. The parent key must use named columns only, which may include >an explicitly named rowid (INTEGER PRIMARY KEY) column." This is because unless the rowid column (the INTEGER PRIMARY KEY) of a standard table is explicitly named, it's value will not be dumped by the dump command, and may be changed by running operations such as VACUUM on the database (since it is not a named data item of the row -- it is merely an internal ephemeral identifier not associated with the rest of the row -- it is not a candidate key for the row). If the INTEGER PRIMARY KEY is explicitly named then it is a "value" in the row and will be dumped by the dump command, and will not change when a vacuum of the database is done since it is a part of the row data (as a named column it is a valid candidate key for the row). Otherwise it is still the "record number" of the row within the table and the behaviour associated with that status is not changed (that is, its value cannot be null and if you attempt to insert a record with a null INTEGER PRIMARY KEY (record number) the value will be set to one greater that the maximum record number in use at the time of insert (or with AUTOINCREMENT, one greater than any record number ever inserted in the table). Also, you (or someone else) may in the future add a column to the table called "rowid" which would mask the original "rowid" thus destroying referential integrity since unless explicitly given a name references to "rowid" are magical. >Which would be more efficient? >1) WITHOUT ROWID and column of INTEGER PRIMARY KEY >or >2) an aliased rowid. No idea since the underlying structures are different between ROWID and WITHOUT ROWID tables. In a ROWID table the INTEGER PRIMARY KEY (the implicit record number) identifies the "row" in the storage B-Tree directly -- you may still have other keys (PRIMARY or UNIQUE) which are merely additional indexes on the table data columns with the RowID being the payload of the index; however, the "record number" INTEGER PRIMARY KEY always exists as the primary identifier of the row. WITHOUT ROWID tables are conceptually just index B-Tree's where the declared PRIMARY KEY is the record key in the index and the other columns are the payload. The primary difference that you would notice is that in a WITHOUT ROWID table you would have to explicitly give each row a unique rowid value rather than allow the database engine to create one for you when you insert data. --- 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
Re: [sqlite] keys
On 25 Aug 2018, at 12:31am, w...@us.net wrote: > "The parent key of a foreign key constraint is not allowed to use the rowid. > The parent key must used named columns only." > > Why is this? Because it's not named. In theory you could later add a column named 'rowid' to mean a BLOB column. > Which would be more efficient? > 1) WITHOUT ROWID and column of INTEGER PRIMARY KEY > or > 2) an aliased rowid. I think it would be (2). Actually define the column 'rowid' as INTEGER PRIMARY KEY. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] keys
got it. Thanks. On 2018-08-24 19:36, J Decker wrote: On Fri, Aug 24, 2018 at 4:31 PM wrote: "The parent key of a foreign key constraint is not allowed to use the rowid. The parent key must used named columns only." Why is this? Which would be more efficient? 1) WITHOUT ROWID and column of INTEGER PRIMARY KEY or 2) an aliased rowid. I expect because rowid is subject to change insert 3 records, delete the first, and 2, 3 become 1 and 2 ... which would have to cascade update ALL other FK refs... Background: The data is sparse, incomplete, and fuzzy. Two records are known to have a relationship even though the content that identifies this relation is not known. Lyle Ward Genealogist ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] keys
On Fri, Aug 24, 2018 at 4:31 PM wrote: > "The parent key of a foreign key constraint is not allowed to use the > rowid. The parent key must used named columns only." > > Why is this? > Which would be more efficient? > 1) WITHOUT ROWID and column of INTEGER PRIMARY KEY > or > 2) an aliased rowid. > I expect because rowid is subject to change insert 3 records, delete the first, and 2, 3 become 1 and 2 ... which would have to cascade update ALL other FK refs... > > Background: The data is sparse, incomplete, and fuzzy. Two records are > known to have a relationship > even though the content that identifies this relation is not known. > > > Lyle Ward > Genealogist > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] keys
"The parent key of a foreign key constraint is not allowed to use the rowid. The parent key must used named columns only." Why is this? Which would be more efficient? 1) WITHOUT ROWID and column of INTEGER PRIMARY KEY or 2) an aliased rowid. Background: The data is sparse, incomplete, and fuzzy. Two records are known to have a relationship even though the content that identifies this relation is not known. Lyle Ward Genealogist ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
