Re: [sqlite] Re: - [sqlite] can anyone reproduce ticket# 1540 (failure to create a primary key)?
> ... and then query the sqlite_master for the > names of other indexes ... Or use PRAGMA index_list(table-name) and PRAGMA index_info(index-name) Regards
RE: [sqlite] Re: - [sqlite] can anyone reproduce ticket# 1540 (failure to create a primary key)?
Thanks to all who replied, I appreciate all your help. The problem is now solved. The ticket 1540 is indeed invalid (the primary key IS created, even when it is not visible in sqlite_master) and I got all the answers I needed. To summarize: for getting information about all the indexes for a table, one should use PRAGMA table_info(table_name) for extracting the primary key, and then query the sqlite_master for the names of other indexes, ignoring any 'sqlite_autoindex_tablename_1' entries. To answer Shane's question (if a constraint should be queriable or not), in my opinion anything that can be set should be queriable, otherwise one could not build an administration tool that works as expected. As far as sqlite is concerned, almost anything can be queried without parsing the SQL. Why 'almost'? I still didn't figure out a way (other than parsing) to query the collation sequence for a field, for example. Done. Now I can finally go to bed. Regards, Bogdan
Re: [sqlite] Re: - [sqlite] can anyone reproduce ticket# 1540 (failure to create a primary key)?
"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_keyINTEGER 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_keyINTEGER 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>
RE: [sqlite] Re: - [sqlite] can anyone reproduce ticket# 1540 (failure to create a primary key)?
> 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? Your assumption is correct. Where you are incorrect is assuming that defining a column as INTEGER PRIMARY KEY results in the creation of an index. See the docs on the web site (particularly the syntax for CREATE TABLE, IIRC) for an explanation. -Tom
RE: [sqlite] Re: - [sqlite] can anyone reproduce ticket# 1540 (failure to create a primary key)?
Thanks for the reply. To answer your question, semi-colons are not needed when executing a single SQL statement. As regarding the other matter, the table is indeed created and accepts insertion of NULL values, but that was not the problem. 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? Regards, Bogdan
