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 ... > >

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.

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

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

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

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); >

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

[sqlite] Unique indexes apparently not working

2012-03-12 Thread Alex Queiroz
Hello, I have a problem with a customer database that is very strange. This is part of the DB's schema: - BEGIN - CREATE TABLE IF NOT EXISTS user_identity ( id INTEGER PRIMARY KEY, shortName TEXT, domainName TEXT,