Re: [sqlite] Index for Primary Key column missing?

[Neville Franks]

Jens,
Thanks for that. I incorrectly assumed because the index was named
xxx_tags it wasn't for the 'tag' column, but related to the table,
which is named 'tags'.

I had read the content you referred to.

Thursday, February 21, 2008, 11:11:19 PM, you wrote:


JM> Am 21.02.2008 um 10:30 schrieb Neville Franks:

>> I have created a table with a column: tag text primary key
>>
>> When I do:
>> sqlite> .indices tags
>> sqlite_autoindex_tags_1
>>
>> I only see the one index which I assume is for the ROWID clm.

JM> No, this is the index for your 'tag' column as can be seen by

sqlite>> EXPLAIN QUERY PLAN SELECT * FROM tags WHERE tag='foo';
JM> 0|0|TABLE tags WITH INDEX sqlite_autoindex_tags_1

JM> (see also , especially the
JM> section "Specifying a PRIMARY KEY..." for more details)


>> I have
>> inserted a row into the table, in case that is relevant.
>>
>> My understanding is that a primary key column will have an index. So
>> my question is where is the index for the 'tag' column?
>>
>> This is the complete CREATE TABLE SQL.
>>
>> CREATE TABLE tags
>> (
>>tag text primary key,
>>description text,
>>date_created text DEFAULT CURRENT_TIMESTAMP,
>>style text
>> )




-- 
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index for Primary Key column missing?

[Jens Miltner]

Am 21.02.2008 um 10:30 schrieb Neville Franks:

> I have created a table with a column: tag text primary key
>
> When I do:
> sqlite> .indices tags
> sqlite_autoindex_tags_1
>
> I only see the one index which I assume is for the ROWID clm.

No, this is the index for your 'tag' column as can be seen by

sqlite> EXPLAIN QUERY PLAN SELECT * FROM tags WHERE tag='foo';
0|0|TABLE tags WITH INDEX sqlite_autoindex_tags_1

(see also , especially the  
section "Specifying a PRIMARY KEY..." for more details)


> I have
> inserted a row into the table, in case that is relevant.
>
> My understanding is that a primary key column will have an index. So
> my question is where is the index for the 'tag' column?
>
> This is the complete CREATE TABLE SQL.
>
> CREATE TABLE tags
> (
>tag text primary key,
>description text,
>date_created text DEFAULT CURRENT_TIMESTAMP,
>style text
> )

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index for Primary Key column missing?

[Neville Franks]

I have created a table with a column: tag text primary key

When I do:
sqlite> .indices tags
sqlite_autoindex_tags_1

I only see the one index which I assume is for the ROWID clm. I have
inserted a row into the table, in case that is relevant.

My understanding is that a primary key column will have an index. So
my question is where is the index for the 'tag' column?

This is the complete CREATE TABLE SQL.

CREATE TABLE tags
(
tag text primary key,
description text,
date_created text DEFAULT CURRENT_TIMESTAMP,
style text 
)
  
Thanks.

-- 
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users