Robert Simpson wrote:
Given the following schema ...
create table foo (
id integer,
otherid integer,
myvalue varchar(50),
constraint foo_1 primary key (id, otherid),
constraint foo_2 unique (myvalue));
create unique index foo_index on foo (otherid);
Three indexes are created. pragma index_list(foo) reveals:
sqlite_autoindex_foo_1
sqlite_autoindex_foo_2
foo_index_3
Furthermore, table_info(foo) reveals:
id integer pk
otherid integer pk
myvalue
So, how do I determine at runtime which index is the primary key index? I
suppose I could keep track of all 3 indexes and what columns are in the
indexes, and match up the column list with all the table_info rows with the
'pk' column set ... but that doesn't help me if some joker goes and adds
this:
create index dupe_index on foo(id, otherid)
Now I have two indexes on the same two columns, but only one index is
considered the "primary key" index.
The solution I came up with is sortof a combination deal. If the index is
called sqlite_autoindex_foo_% and contains all the columns with pk=1 in
table_info(foo), then it must be the primary key index.
Is this a safe assumption or is there an easier way to do this?
Robert
Robert,
I think you should be safe with this scheme. SQLite prevents a user from
creating an index named sqlite_autoindex_*, so any index named that way,
and containing all the primary key columns and only the primary key
columns, must be the primary key.
The only gotcha I see is the fact that a integer primary key column
doesn't generate an autoindex at all. You will have to infer the
existence of this index this from the primary key flag on the column,
and the lack of an autoindex on that column only.
HTH
Dennis Cote
