Primary Key v Unique Index
(1) By curmudgeon on 2021-02-04 15:04:37 [link] [source]
If I do this
CREATE TABLE t(id INTEGER PRIMARY KEY, .... );
then id is the same as rowid.
If I do this
CREATE TABLE t(id INTEGER, ...);
CREATE UNIQUE INDEX id on t(id);
is that the same thing or is id different from rowid.
If the latter, is it possible to add a primary key after the fact? From what I've read I would guess not but I keep reading it's faster to build indexes after inserting large amounts of data but that wouldn't be possible in the case of an INTEGER PRIMARY KEY.
(2) By Gunter Hick (gunter_hick) on 2021-02-04 15:17:28 in reply to 1 [link] [source]
The INTEGER PRIMARY KEY phrase is magic and allows you to retrieve the internal rowid of a row in a rowid table using a user-defined name.
Your second example does not use the magic incanttion, so the field is something comletely disconnected from the internal rowid (which you can still access via any of the default names).
You cannot change from non-magic to magic definition. If this were possible, you would be overwriting the id you specified when inserting the record with SQLite's idea of the rowid and probably lose referential integrity on any foreign key that references that field.
(3) By curmudgeon on 2021-02-04 15:24:45 in reply to 2 [link] [source]
Thanks Gunter. It's what I expected but my OCD demanded confirmation.
(4) By David Raymond (dvdraymond) on 2021-02-04 15:39:46 in reply to 1 [link] [source]
Consider that "integer primary key" gives a name to something that's already there for the table to begin with. So including the integer primary key definition at the start isn't going to slow it down in the same way as adding the other indexes at the start vs at the end.
(Though, if I recall correctly, with an "integer primary key", if you insert your IDs in random order it'll be a little slower than without the "integer primary key", as without it the rowids can just be assigned in insertion order, meaning less Btree manipulation as you go. But if you insert your "integer primary key" data in primary key order then it'll be the same, and save you the cost of building the extra index later)
(5) By Richard Damon (RichardDamon) on 2021-02-05 02:10:20 in reply to 2 [link] [source]
My understanding from https://sqlite.org/lang_createtable.html Section 3.5 paragraph 2
If a table has a single column primary key and the declared type of that column is "INTEGER" and the table is not a WITHOUT ROWID table, then the column is known as an INTEGER PRIMARY KEY. See below for a description of the special properties and behaviors associated with an INTEGER PRIMARY KEY.
It isn't just the literal phrase "INTEGER PRIMARY KEY" that is magic, but as long as the type of the column is INTEGER (and only that spelling, not just anything with integer affinity) and is made to be THE primary key, even by a primary key constraint clause later in the table definition (but at the initial time of creation), that the column becomes that special alias to the ROWID.
(6) By Gunter Hick (gunter_hick) on 2021-02-05 07:13:35 in reply to 5 [source]
Yes, the incantation rules have been relaxed. You can now say CREATE TABLE ... (id INTEGER, <insert lots of fields and constraints>, PRIMARY KEY (id)); and get the same effect. I would still recommend using the original incantation. OT: Watching hollywood films gives the impression that Latin was dropped from common use because it was too easy to inadvertently summon a demon during normal conversation.