Re: [sqlite] Unable to create two indexes with the same name but on different tables

[Richard Hipp]

On 9/30/16, Malte Legenhausen  wrote:
>  Other database systems
> like postgres can differ between indexes with the same name on different
> tables.

No it can't.  Index names must be unique within a schema.  Have you
actually tried creating two indexes with the same name on PostgreSQL?
(I did just to confirm that it gives an error.)
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unable to create two indexes with the same name but on different tables

[Simon Slavin]

On 30 Sep 2016, at 2:33pm, Malte Legenhausen  wrote:

> sqlite> create table t1(one varchar(10));
> sqlite> CREATE INDEX unique_one ON t1 (one);
> sqlite> create table t2(one varchar(10));
> sqlite> CREATE INDEX unique_one ON t2 (one);
> Error: index unique_one already exists
> 
> This is an unintended behaviour cause the following command returns with an 
> empty result:
> 
> sqlite> PRAGMA INDEX_LIST(t2);

Index names have to be unique to the entire database, not just the table 
they're indexing.  You are explicitly looking just for indexes to the t2 table.

If you want to check whether an index name is in use use

PRAGMA index_info(index-name)

If you get a blank response then no such index exists.

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

Re: [sqlite] Unable to create two indexes with the same name but on different tables

[Paul Sanderson]

Sorry what is "very hard" about

SELECT * FROM sqlite_master WHERE type = 'index' AND name = 'new_index_name'

to see if the index already exists
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 30 September 2016 at 14:33, Malte Legenhausen  wrote:
> Hi,
>
>
>
> Could someone please explain why the following sequence of commands fail in 
> sqlite?
>
>
>
> SQLite version 3.14.0
>
>
>
> sqlite> create table t1(one varchar(10));
>
> sqlite> CREATE INDEX unique_one ON t1 (one);
>
> sqlite> create table t2(one varchar(10));
>
> sqlite> CREATE INDEX unique_one ON t2 (one);
>
> Error: index unique_one already exists
>
>
>
> This is an unintended behaviour cause the following command returns with an 
> empty result:
>
> sqlite> PRAGMA INDEX_LIST(t2);
>
>
>
> Debugging this behaviour is very hard cause to make sure an index name is not 
> taken would result in looking in all indexes of all other existing tables. I 
> hope this is not an intended behaviour? Other database systems like postgres 
> can differ between indexes with the same name on different tables.
>
>
>
> If it is intended would it be possible to enhance the documentation?
>
>
>
> Best regards
>
> Malte
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unable to create two indexes with the same name but on different tables

[Malte Legenhausen]

Hi,

 

Could someone please explain why the following sequence of commands fail in 
sqlite?

 

SQLite version 3.14.0

 

sqlite> create table t1(one varchar(10));

sqlite> CREATE INDEX unique_one ON t1 (one);

sqlite> create table t2(one varchar(10));

sqlite> CREATE INDEX unique_one ON t2 (one);

Error: index unique_one already exists

 

This is an unintended behaviour cause the following command returns with an 
empty result:

sqlite> PRAGMA INDEX_LIST(t2);

 

Debugging this behaviour is very hard cause to make sure an index name is not 
taken would result in looking in all indexes of all other existing tables. I 
hope this is not an intended behaviour? Other database systems like postgres 
can differ between indexes with the same name on different tables.

 

If it is intended would it be possible to enhance the documentation?

 

Best regards

Malte

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