Re: [sqlite] Unique indexes apparently not working
Hello, On Mon, Mar 12, 2012 at 11:52 PM, Simon Slavinwrote: > > 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
On 12 Mar 2012, at 10:11pm, Alex Queirozwrote: > - 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
Hello, On Mon, Mar 12, 2012 at 11:34 PM, Petite Abeillewrote: > > > 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
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
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
Hallo, On Mon, Mar 12, 2012 at 11:16 PM, Petite Abeillewrote: > > 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
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
[sqlite] Unique indexes apparently not working
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, lastUpdated INTEGER, ucgID INTEGER ); CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByID' ON 'user_identity' (id ASC); CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByUcgID'ON 'user_identity' (shortName ASC, ucgID ASC); -- END -- 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. At this point I really have no idea of what could have caused that. If anyone has seen something similar before, please share. :) Cheers, -- -alex http://www.artisancoder.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users