"Bogdan Ureche" <[EMAIL PROTECTED]> writes:
> I am beginning to believe that maybe I was wrong in my assumption that 'if a
> table has an index, that index shows in sqlite_master'. Then my problem is
> now to find another way to get index information for a table. Any
> suggestions?
An index for an INTEGER PRIMARY KEY does not show in sqlite_master. Instead,
you can find the primary key of a table using:
PRAGMA table_info(table_name);
The field(s) marked with pk=1 are primary key field(s).
% sqlite3 /tmp/xxx.db
SQLite version 3.2.1
Enter ".help" for instructions
sqlite> CREATE TABLE test_table
...> (
...> my_primary_key INTEGER PRIMARY KEY,
...> some_other_field TEXT
...> );
sqlite> .mode line
sqlite> PRAGMA table_info(test_table);
cid = 0
name = my_primary_key
type = INTEGER
notnull = 0
dflt_value =
pk = 1
cid = 1
name = some_other_field
type = TEXT
notnull = 0
dflt_value =
pk = 0
With more than one field composing the primary key:
sqlite> CREATE TABLE t2 (f1 INTEGER, f2 TEXT, f3 TEXT, PRIMARY KEY(f1, f2));
sqlite> PRAGMA table_info(t2);
cid = 0
name = f1
type = INTEGER
notnull = 0
dflt_value =
pk = 1
cid = 1
name = f2
type = TEXT
notnull = 0
dflt_value =
pk = 1
cid = 2
name = f3
type = TEXT
notnull = 0
dflt_value =
pk = 0
sqlite>
With a non INTEGER PRIMARY KEY (which you *can* have, contrary to a comment
made previously by someone):
sqlite> CREATE TABLE t3 (f1 TEXT PRIMARY KEY, f2 INTEGER);
sqlite> PRAGMA table_info(t3);
cid = 0
name = f1
type = TEXT
notnull = 0
dflt_value =
pk = 1
cid = 1
name = f2
type = INTEGER
notnull = 0
dflt_value =
pk = 0
Note that any primary key fields *other than* a single INTEGER PRIMARY KEY are
also in sqlite_master:
sqlite> select * from sqlite_master;
type = table
name = test_table
tbl_name = test_table
rootpage = 2
sql = CREATE TABLE test_table
(
my_primary_key INTEGER PRIMARY KEY,
some_other_field TEXT
)
type = table
name = t2
tbl_name = t2
rootpage = 3
sql = CREATE TABLE t2 (f1 INTEGER, f2 TEXT, f3 TEXT, PRIMARY KEY(f1, f2))
type = index
name = sqlite_autoindex_t2_1
tbl_name = t2
rootpage = 4
sql =
type = table
name = t3
tbl_name = t3
rootpage = 5
sql = CREATE TABLE t3 (f1 TEXT PRIMARY KEY, f2 INTEGER)
type = index
name = sqlite_autoindex_t3_1
tbl_name = t3
rootpage = 6
sql =
sqlite>
