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? --Ned. http://nedbatchelder.com
