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] can anyone reproduce ticket# 1540 (failure to create a primary key)?
I'm going to state right up front that I have very little experience with sqlite and, if I can help demonstrate that this ticket is valid, I will. That said, it comes as no surprise that I had never looked at sqlite_master before. So, I checked out the contents and found that, with my very simple test database, there is only one *_autoindex_* and that was on the table which has a BLOB for the primary key. All tables that had integer primary keys did not have any such index. I'm not sure why an autoindex would be expected on a table for which a physical integer column was declared to be the primary key. As for detecting the column which has the primary key, that is not a typical functionality provided by a database subsystem. Contraints exist to maintain consistency and are typically not queryable (at least not in my experience). Maybe this is typically queryable and I've just never had a need that exposed me to it. My uninformed guess is that the autoindex that you would see when putting a UNIQUE constraint on the second column is on the second column for more efficient constraint checking. Again, I don't really know how the autoindex is used or anything so I may be full of it. On Mon, 28 Nov 2005, Bogdan Ureche wrote: > Will, > > Thanks for the reply. Yes, the table gets created, however the primary key > (sqlite_autoindex_tablename_1) if it is created, is not visible as a > distinct row in sqlite_master. > > Are you saying that the index is created even if it is not added to > sqlite_master? If the answer is yes, then how could one find the primary key > for a table (preferably without parsing sqlite_master.SQL)? > > Regards, > > Bogdan >
Re: [sqlite] can anyone reproduce ticket# 1540 (failure to create a primary key)?
Thanks for the reply. Yes, the table gets created, however the primary key (sqlite_autoindex_tablename_1) if it is created, is not visible as a distinct row in sqlite_master. Are you saying that the index is created even if it is not added to sqlite_master? If the answer is yes, then how could one find the primary key for a table (preferably without parsing sqlite_master.SQL)? As I understand it, sqlite always creates a row id: read http://www.sqlite.org/version3.html "64-bit ROWIDs" and http://www.sqlite.org/autoinc.html There is no need to create a separate index for INTEGER PRIMARY KEY, because that's the "natural order" of the table Therefore that' the most efficient "index". Michael
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] can anyone reproduce ticket# 1540 (failure to create a primary key)?
I have the same question. Specifically, what indication is there that no primary key was created. Certainly, by executing the command below (using the sqlite3 command tool) verbatim and following that with ".schema" indicates that the table was created with a primary key. This is with 3.2.7 on Windows XP. On Mon, 28 Nov 2005, Will Leshner wrote: > > On Nov 28, 2005, at 6:39 PM, Bogdan Ureche wrote: > > > CREATE TABLE TestTable (TestField1 INTEGER PRIMARY KEY, TestField2 > > VARCHAR) > > > Does the table itself get created? It is impossible to have a table > without an INTEGER PRIMARY KEY in SQLite, so it is hard to believe > that you can create the table without an INTEGER PRIMARY KEY. How can > you tell the key isn't created? >
