SQLite Forum

Why do I have a `SQLITE_AUTOINDEX` index for my primary key? Is the table itself not an index on the PK?
Login

Why do I have a `SQLITE_AUTOINDEX` index for my primary key? Is the table itself not an index on the PK?

(1) By aryairani on 2021-05-05 08:06:25 [link] [source]

Hello!

I've got this table:

CREATE TABLE hash (
  id INTEGER PRIMARY KEY NOT NULL,
  base32 TEXT UNIQUE NOT NULL
);
CREATE INDEX hash_base32 ON hash(base32);

and sqlite3_analyzer tells me this:

*** Page counts for all tables and indices separately *************************

HASH_BASE32....................................... 15          14.3% 
SQLITE_AUTOINDEX_HASH_1........................... 15          14.3% 
HASH.............................................. 14          13.3% 

My question: Isn't HASH (the table) already the btree that serves as the index on the primary key id? Why is there a separate SQLITE_AUTOINDEX_HASH_1? I'd like my db to be that much smaller if one of these is redundant, and I'd love to better understand what's going on.

Thanks in advance, Arya

(2) By TripeHound on 2021-05-05 08:53:58 in reply to 1 [link] [source]

That index is to enforce the UNIQUE constraint on base32.

(3) By Ryan Smith (cuz) on 2021-05-05 11:02:38 in reply to 1 [link] [source]

Tripehound is correct about why that index exists, but it is perhaps prudent to add that your request for the column to be UNIQUE prompts the creation of the auto index, which means that your explicit CREATE INDEX after the table is completely superfluous and does nothing other than waste some space - best to remove it.

To be clear, this:

CREATE TABLE hash (
  id INTEGER PRIMARY KEY NOT NULL,
  base32 TEXT UNIQUE NOT NULL
);
is semantically equivalent to this:
CREATE TABLE hash (
  id INTEGER PRIMARY KEY NOT NULL,
  base32 TEXT NOT NULL
);
CREATE UNIQUE INDEX hash_base32 ON hash(base32);
The only difference being that in the listed indexes, the second one will show the hash_base32 name in stead of the autoindex name.

(4) By aryairani on 2021-05-05 16:18:47 in reply to 3 [link] [source]

Thank you!

(5) By niklasb on 2021-05-06 12:35:35 in reply to 3 [source]

Those do not seem equivalent to me.

In the first schema the UNIQUE on base32 ensures that base32 is unique.

In the second schema the hash value of base32 is unique instead.

(6) By David Raymond (dvdraymond) on 2021-05-06 13:47:43 in reply to 5 [link] [source]

In the second schema the hash value of base32 is unique instead.

CREATE INDEX hash_base32 ON hash(base32)

There "hash" is the table name and "base32" is the field name.

It's not "hash" as a function name and "base32" as an argument.

CREATE INDEX