CODEWITHSUNDEEP
sqlite
Alexey Romanov
Alexey Romanov

Reputation: 170755

Is there a way to tell what columns an SQLite internal index is on?

sqlite> create table foo(x TEXT PRIMARY KEY, y TEXT UNIQUE);
sqlite> select * from sqlite_master;
table|foo|foo|2|CREATE TABLE foo(x TEXT PRIMARY KEY, y TEXT UNIQUE)
index|sqlite_autoindex_foo_1|foo|3|
index|sqlite_autoindex_foo_2|foo|4|

For user-defined indices, the last column of sqlite_master tells me what columns they are on. But as you can see, for the indices implicitly created by (non-rowid) PRIMARY KEY and UNIQUE, this information is absent. Is there a reliable way for me to tell which autoindex is which?

Upvotes: 1

Views: 362

Answers (1)

CL.
CL.

Reputation: 180081

There is no difference between internal and explicitly-created indexes; you can get information about them with the same PRAGMAs (index_list, index_info, index_xinfo):

sqlite> pragma index_list(foo);
seq         name                    unique      origin      partial
----------  ----------------------  ----------  ----------  ----------
0           sqlite_autoindex_foo_2  1           u           0
1           sqlite_autoindex_foo_1  1           pk          0
sqlite> pragma index_xinfo(sqlite_autoindex_foo_1);
seqno       cid         name        desc        coll        key
----------  ----------  ----------  ----------  ----------  ----------
0           0           x           0           BINARY      1
1           -1                      0           BINARY      0
sqlite> pragma index_xinfo(sqlite_autoindex_foo_2);
seqno       cid         name        desc        coll        key
----------  ----------  ----------  ----------  ----------  ----------
0           1           y           0           BINARY      1
1           -1                      0           BINARY      0

Upvotes: 2

Related Questions