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: [email protected]
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
