SQLite Forum

Non primary key alias to rowid
Login

Non primary key alias to rowid

(1.1) By Marco Bubke (marcob) on 2020-06-04 14:23:13 edited from 1.0 [link]

Hello

I used the rowid with the INTEGER PRIMARY KEY as an alias but with the session api
I had to use a different primary key because otherwise I could not track deleted
entries. But now I want to use FOREIGN KEYS too. And anyway rowid is not stable. Is there a way to map the rowid to a INTEGER UNIQUE column? Or what is the best to create a id column which is generating unique default integer ids(like rowid)?

Here some snippets from the table design:
CREATE TABLE IF NOT EXISTS translatables(id INTEGER NOT NULL UNIQUE, translationTextId TEXT PRIMARY KEY NOT NULL CHECK (translationTextId 
!= ''), screenId TEXT, defaultText TEXT)

CREATE TABLE IF NOT EXISTS translations(id INTEGER NOT NULL UNIQUE, 
translatableId INTEGER NOT NULL REFERENCES translatables(id) ON DELETE 
CASCADE, language TEXT NOT NULL CHECK (language != ''), translation 
TEXT, PRIMARY KEY(translatableId, language))


Thanks and best regards, Marco

(2) By Gunter Hick (gunter_hick) on 2020-06-04 11:35:51 in reply to 1.0 [link]

Tables declared as WITHOUT ROWID do not have a rowid; instead, they need to have a PRIMARY KEY declared.

In ROWID Tables, the undeclared names rowid, oid and _rowid_ all refer to the internal rowid, unless they are overloaded by a declared column. Additionally, the single column declared as INTEGER PRIMARY KEY can be added as an alias for the internal rowid.

Internal rowids are UNIQUE by design. As long as the row is not deleted, it retains its rowid. It is only when you delete a row by PRIMARY KEY and insert a new row with the same PRIMARY KEY that the rowid may appear to have changed.

For efficient use of a foreign key, it should be the PRIMARY KEY of the parent table (for fast lookup) and there should be an index on the child table (for enforcing ON DELETE etc. clauses). Make the latter index UNQIUE to allow not more than one entry in the child table; make the latter index NOT NULL to force all rows in the child table to have a parent.

(4) By David Raymond (dvdraymond) on 2020-06-04 13:55:27 in reply to 2 [link]

> As long as the row is not deleted, it retains its rowid.

This is only true if the rowid is used as an "integer primary key". If it's a regular rowid table where the rowid isn't aliased, then vacuum can change all the rowids for every record.

(6) By Marco Bubke (marcob) on 2020-06-04 14:34:13 in reply to 2 [link]

First I need the ROWID for the update hook. Second I need the PRIMARY KEY of a text field for the sessions. Third I need an integer id for efficiency in the child table because the PRIMARY KEY in the parent table is large. Then I cannot use ROWID for it because it is unstable and does not work together with the FOREIGN KEYS. So I think what I really need is an alias of ROWID which is not a PRIMARY KEY. Like you see in this examples I have an extra unique index which could be an alias to ROWID but this is impossible because I use already an other
PRIMARY KEY. I am not an expert in Sqlite, so maybe somebody knows a better way.

CREATE TABLE IF NOT EXISTS translatables(id INTEGER NOT NULL UNIQUE, translationTextId TEXT PRIMARY KEY NOT NULL CHECK (translationTextId != ''), screenId TEXT, defaultText TEXT)

CREATE TABLE IF NOT EXISTS translations(id INTEGER NOT NULL UNIQUE, 
translatableId INTEGER NOT NULL REFERENCES translatables(id) ON DELETE 
CASCADE, language TEXT NOT NULL CHECK (language != ''), translation 
TEXT, PRIMARY KEY(translatableId, language))

(7) By Richard Damon (RichardDamon) on 2020-06-04 14:55:11 in reply to 6 [link]

Why do you need a Text primary key for sessions? I think that is the problem with your definition.

A Text 'Primary Key' for a table using row-ids is not really the primary key for the table, but the real primary key will be the row-id, and the text key will really just be a unique key.

There is no such thing as a non-primary key alais for the ROWID, as by definition if the ROWID exists in the table, it IS the Primary Key for the table.

(9) By Marco Bubke (marcob) on 2020-06-04 17:08:47 in reply to 7 [link]

Because if remove the it locally and but get it externally again as an import you have two different rowids. So after an import your changeset is not deleting it if it is not using the global text it. So your global id which is used everythere has to be the primary key. It's only working if your global key is a text id and this is a primary key.

(11) By Richard Damon (RichardDamon) on 2020-06-04 19:17:00 in reply to 9 [link]

I will admit that I am not familiar with sessions, but if sessions can't make the changesets based on the text field unique key then you solution may be to make the text field the primary key and live with the slight efficiency hit. You could perhaps minimize that and make the table a WITHOUT ROWID table, so the text key IS the real primary key, and thus omit the second lookup. 

You would also probably want to make that text field be the foreign key, as if it can't deal with the same text key mapping to different integer primary keys, are you sure it can handle matching by a differing secondary unique key from the two sources for the same primary key?

It also might be possible to pre-process looking at the text key and renumbering (with cascade update) if you find different ROWID  for the same text value.

(14) By Marco Bubke (marcob) on 2020-06-05 07:06:04 in reply to 11 [link]

I need the RowId for the update hook, I had actually the same idea like you but RowId is hardcoded to many C APIs. The text id is quite long, so I thought it would better not to use it as a foreign key. Actually I do not understand your last sentence. But it looks like there is no easy way to get rid off the extra unique id. I can live with that. Like I said I am not an expert, especially to the session extension, and I am trying to avoid to do a really stupid mistake. Thank you for you help. :-)

(19) By Richard Damon (RichardDamon) on 2020-06-05 14:07:19 in reply to 14 [link]

I am not an expert in the session extension either (I haven't used it at all), and I am a bit surprised that it can't handle the case of the unique identification of a data row being a unique text field instead of the somewhat arbitrary ROWID primary key.

If this is the case, my concept of a prefilter would be then when you bring the changeset in, you first fix your database to correct for ROWID issues with your text key. Assuming the changeset set comes in as an SQLite database, first, do a join to find records with the same ROWID but different text keys, and renumber those rows to some value unused in either the local database or the changeset. Then do a join to find records with the same text key but different ROWID, and renumber the ROWID in your local database to match. 

Now, you should have the case that ROWIDs and the text key are consistent, so tracking changes in one will track changes in the other. You MUST have your ROWID made explicit as an INTEGER PRIMARY KEY so a vacuum won't destroy numbering, but since the whole purpose of this was for FOREIGN KEYS that is basically assumed.

(8.1) By Keith Medcalf (kmedcalf) on 2020-06-04 15:32:27 edited from 8.0 in reply to 6 [link]

PRIMARY KEY is just an alternate spelling for UNIQUE *EXCEPT* that in a RowID table the specific declaration INTEGER PRIMARY KEY spelled EXACTLY like that means that you have "named" the RowID column and it is now a "named column" and behaves like any other named column EXCEPT that it is the automatically assigned RowID (cannot be null).

The actual primary key of a RowID table is the RowID which may be either magical (not named and therefore cannot be used in FOREIGN KEY constraints and subject to "not being preserved" if you VACUUM the table or dump/load or "invisible" if you SELECT * from the table).  INTEGER PRIMARY KEY (the rowid) is also never NULL.  If you attempt to store a NULL value in that column it will be autoassigned the next available integer value.

You can have the RowID be a "named column" (after which it behaves like a named column with a meaningful value that can be used in FOREIGN KEY constraints and is preserved when the table is vacuumed and is returned by SELECT * just like other non-magical/non-hidden/non-invisible columns).

In a RowID table any column that is a PRIMARY KEY but not an INTEGER PRIMARY KEY (of which there can only be one, since it is the RowID) is merely a UNIQUE constraint and it is not constrained NOT NULL unless you specify that constraint.

(10) By Marco Bubke (marcob) on 2020-06-04 17:12:16 in reply to 8.1 [link]

Yes, I know I read it in the documentation. But the rowid is special because this is what you get in callbacks etc.. So it would be nice if you could map the rowid simply to an unique index.

(12) By Keith Medcalf (kmedcalf) on 2020-06-04 23:38:21 in reply to 6 [link]

For a RowID table the RowID *IS* the primary key and there is absolutely nothing you or anyone else can do about it.  This is simply the way it works.  Deal with it.

Other than an INTEGER PRIMARY KEY (which is an explicit declaration of the RowID) the phrase "PRIMARY KEY" is absolutely nothing more than an alternate way of spelling UNIQUE.

So, your declarations would be:

CREATE TABLE IF NOT EXISTS translatables(id INTEGER PRIMARY KEY, translationTextId TEXT UNIQUE NOT NULL CHECK (translationTextId != ''), screenId TEXT, defaultText TEXT);

Mutatis mutandis your other tables.

(13) By Marco Bubke (marcob) on 2020-06-05 06:53:19 in reply to 12 [link]

This was my original design and it is not working with sessions in all cases. For example if there is a change set from an other database the global key has to be used. In this cases translationTextId. It would be nice if if you could write:

CREATE TABLE IF NOT EXISTS translatables(id AS (rowid), translationTextId TEXT PRIMARY KEY NOT NULL CHECK (translationTextId != ''), screenId TEXT, defaultText TEXT);

(15) By Keith Medcalf (kmedcalf) on 2020-06-05 08:13:27 in reply to 13

Aha!  I understand your conundrum now after looking at the sessions module more closely.  So what you want is to be able to define a column that "acts like" it were the INTEGER PRIMARY KEY, or RowID, but is not.

You can do this with triggers and ignore the RowID entirely.  Example:

```
CREATE TABLE IF NOT EXISTS translatables
(
     id                INTEGER UNIQUE, 
     translationTextId TEXT PRIMARY KEY NOT NULL CHECK (translationTextId != ''),
     screenId          TEXT,
     defaultText       TEXT
);

CREATE TRIGGER translatables AFTER INSERT ON translatables FOR EACH ROW WHEN id IS NULL
BEGIN
   UPDATE translatables 
      SET id = coalesce((select max(id) + 1 from translatables), 1)
     FROM translatables
    WHERE rowid == new.rowid;
END;
```

So when you try to insert a record in the table with no "id" the id will be set to the value one greater than the current max value of id in the table, starting with 1 for the 1st row.  (Just the same as RowID).  However it is NOT the RowID, so you cannot refer to it as such, nor can you expect to get the value by using `last_insert_rowid()`, for example.  You will have to actually retrieve the value of the ID after inserting records.  It will however be stable and usable in foreign key constraints.

(16.1) By Keith Medcalf (kmedcalf) on 2020-06-05 08:30:06 edited from 16.0 in reply to 15 [link]

Deleted

(18) By Marco Bubke (marcob) on 2020-06-05 09:43:16 in reply to 15 [link]

I have done something similar. I get max(id) before I upsert and as I do all my upserts I do ++id. I need the rowid too because I capture all upserts with the update hook to delete all not upserted entries. carray is very handy for that. So I still think I would be nice to have somthing like CREATE TABLE foo(id AS (rowid), gobalID TEXT PRIMARY KEY, data...).

Thank you much for your help!

(17) By Keith Medcalf (kmedcalf) on 2020-06-05 08:23:34 in reply to 13 [link]

Yeah, you cannot use the implicit rowid in a computed column.  You can use an explicit rowid -- but if you have an explicit rowid then defining a computed column that is nothing more than a copy of the rowid would be a redundant redundancy.

(3) By Richard Damon (RichardDamon) on 2020-06-04 12:36:55 in reply to 1.0 [link]

While the implicitly created rowid column might not be stable (I think it can be changed when doing things like a vacuum) an explicitly declared alias (like with INTEGER PRIMARY KEY) will be stable, and its value not changed, so it is suitable for being referenced by other tables. If you need to make sure that the old values of the primary key are not reused, you can add the autoincrement option to the key (although, you will still be able to manually reuse an old ID number).

To my knowledge, in SQLite, only the primary key has the ability to autofill with unique values, other columns can be made unique, so you can't insert duplicates, but only the primary key has the ability to be auto-filled with unique values.

The best way to get get a column that will always have unique values is to make the table have an INTEGER PRIMARY KEY, and as far as I know, such a table can be used with sessions.

(5.1) By Keith Medcalf (kmedcalf) on 2020-06-04 14:34:45 edited from 5.0 in reply to 1.0 [link]

> And anyway rowid is not stable.

This is incorrect.  RowID is 100% absolutely and completely stable.  

The RowID changes if and only if _**YOU**_ change it.  

The ways that you may change it are with an UPDATE statement or by performing a VACUUM on a database which contains a table that has a non-preserved (non-explicitly named) RowID, or _**YOU**_ otherwise fail to preserve the RowID, for example when you dump/load data from a table.

The only way to make a table column behave like a RowID is to declare it as a rowid using the "INTEGER PRIMARY KEY" declaration.  You may also make it "AUTOINCREMENT" if you like, but there is an almost 100% absolute certainty that you do not need to use the AUTOINCREMENT keyword (which actually has nothing whatsoever to do with auto-incrementing -- rather it should be called MONOTONIC, as in "INTEGER PRIMARY KEY MONOTONIC" since that is what it actually does -- pehaps in your particular case this is what you are looking for).

Only "named columns" can be used in foreign key relationships so if you wish to use the "RowID" in a foreign key relationship you must explicitly declare it.  The contents of all "named columns" are automatically preserved on vacuum.

<https://sqlite.org/rowidtable.html>  
<https://sqlite.org/autoinc.html>