Re: [sqlite] Unique indexes apparently not working

2012-03-12 Thread Alex Queiroz 
Hello,

On Mon, Mar 12, 2012 at 11:52 PM, Simon Slavin  wrote:
>
> On 12 Mar 2012, at 10:11pm, Alex Queiroz  wrote:
>
>> - If I try to reindex the table, it fails;
>> - If I drop the index and try to add it again, it fails.
>
> For both the above ...
>
> What command are you issuing, and what result are you getting from SQLite 
> when it fails ?  (i.e. what is it doing instead of working ?)
>

sqlite> reindex 'user_identity';
Error: indexed columns are not unique

sqlite> drop index userIdentityByUcgID;
sqlite> CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByUcgID'ON
'user_identity' (shortName ASC, ucgID ASC);
Error: indexed columns are not unique

Cheers,
-- 
-alex
http://www.artisancoder.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique indexes apparently not working

2012-03-12 Thread Simon Slavin 

On 12 Mar 2012, at 10:11pm, Alex Queiroz  wrote:

> - If I try to reindex the table, it fails;
> - If I drop the index and try to add it again, it fails.

For both the above ...

What command are you issuing, and what result are you getting from SQLite when 
it fails ?  (i.e. what is it doing instead of working ?)

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

Re: [sqlite] Unique indexes apparently not working

2012-03-12 Thread Alex Queiroz 
Hello,

On Mon, Mar 12, 2012 at 11:34 PM, Petite Abeille
 wrote:
>
>
> Hmmm… really? That would be most peculiar...
>

Indeed. I have now run this:

sqlite> PRAGMA integrity_check;
rowid 192697 missing from index userIdentityByUcgID
rowid 192701 missing from index userIdentityByUcgID
rowid 192705 missing from index userIdentityByUcgID
rowid 192710 missing from index userIdentityByUcgID
rowid 192711 missing from index userIdentityByUcgID
rowid 192712 missing from index userIdentityByUcgID
rowid 192716 missing from index userIdentityByUcgID
...
...
...

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

Re: [sqlite] Unique indexes apparently not working

2012-03-12 Thread Jean-Christophe Deschamps 



CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByUcgID'ON
'user_identity' (shortName ASC, ucgID ASC);


As a general rule you should reserve single quotes to string litterals. 
Either leave schema names alone (no whitespace, not keyword) or use [my 
pretty table], "my favorite table", `my non-standard unique index on 
(int) table, that I myself made for me only`.




The problem is that a customer has a database where (shortName, ucgID)
have duplicates.

- When I try to insert manually a new record, the index is enforced;
- When I try to update a column, the index is enforced;
- If I try to reindex the table, it fails;
- If I drop the index and try to add it again, it fails.


What does an integrity check say?


At this point I really have no idea of what could have caused that.


An hex editor? 


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

Re: [sqlite] Unique indexes apparently not working

2012-03-12 Thread Petite Abeille 

On Mar 12, 2012, at 11:11 PM, Alex Queiroz wrote:

> The problem is that a customer has a database where (shortName, ucgID)
> have duplicates.

Hmmm… really? That would be most peculiar...

In any case, what does the following statement returns?

select shortName, ucgID, count( * ) from user_identity group by shortName, 
ucgID having count( * ) > 1

If it returns anything… are you sure you have a unique index in the first place?

PRAGMA index_info( userIdentityByUcgID )

If both answers are yes, well, then, congratulation… you managed to badly 
confuse SQLite :D





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

Re: [sqlite] Unique indexes apparently not working

2012-03-12 Thread Alex Queiroz 
Hallo,

On Mon, Mar 12, 2012 at 11:16 PM, Petite Abeille
 wrote:
>
> On Mar 12, 2012, at 11:11 PM, Alex Queiroz wrote:
>
>>       id                      INTEGER PRIMARY KEY,
>> CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByID'       ON 
>> 'user_identity' (id ASC);
>
> Not directly related to your problem, but… these two clauses are redundant… a 
> primary key is unique by definition… no point adding another unique index on 
> top of it...

You are right and I know it, this code was there before I started here. :)

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

Re: [sqlite] Unique indexes apparently not working

2012-03-12 Thread Petite Abeille 

On Mar 12, 2012, at 11:11 PM, Alex Queiroz wrote:

>   id  INTEGER PRIMARY KEY,
> CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByID'   ON 'user_identity' 
> (id ASC);

Not directly related to your problem, but… these two clauses are redundant… a 
primary key is unique by definition… no point adding another unique index on 
top of it...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users