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:
sqlite> create table foo(a int, b integer primary key, c int); sqlite> pramga table_info(foo); cid name type notnull dflt_value pk ---------- ---------- ---------- ---------- ---------- ---------- 0 a int 0 0 1 b integer 0 1 2 c int 0 0 sqlite> pragma index_list(foo); sqlite> Maybe parsing the SQL from sqlite_master is the way to go after all.. :-( --Ned. http://nedbatchelder.com -----Original Message----- From: Ned Batchelder [mailto:[EMAIL PROTECTED] Sent: Sunday, January 23, 2005 12:55 PM To: sqlite-users@sqlite.org Subject: [sqlite] Determining the primary key of a table? 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