One more twist I just discovered:
If the primary key is a single column that auto-increments, there is no information in the pragma index_list about the primary key at all:
That is because there isn't a seperate index on the table using that column. That column is the key for the btree used to store the table data. It can be used to locate records as fast as using an index.
This condition can be inferred when the table has a single column that is marked as part of the primary key, and there is no corresponding index on that column.
I need to examine the schema of a SQLite database programmatically. I've managed to find everything I need in the various pragmas for querying the schema, except: the order of the columns in the primary key.
pragma table_info tells me which columns are in the primary key, but not their order in the key.
pragma index_info tells me the order of columns in the index, but not which index is the primary key.
pragma index_list tells me all the indexes on a table, but not which index is the primary key.
It looks like the first index in index_list which is named "sqlite_autoindex_*", and is unique, is the primary key, but can I be guaranteed of that?
What would be ideal is if the pk column in pragma table_info was not just 0 or 1, but was 0 for columns not in the primary key, and 1 through n for the columns in the primary key, with the value determining their ordering. I understand that represents a slight backward compatibility problem.
Is there something I've missed? Does anyone have a better way (other than parsing the table SQL) to determine the primary key?
You need to scan all the indexes returned by the index_list pragma, and for each one call use the index_info pragma to get the list of indexed columns and their order. The primary key is the one that contains all the columns marked as members of the primary key by the table_info pragma, and no other columns. The order of the columns in the primary key is given by the seqno column in the result of the index_info pragma.
HTH Dennis Cote
