Re: [sqlite] sqlite bug after CREATE UNIQUE INDEX

[Keith Medcalf]

I think the point is that attempting to create the unique index should fail 
(with an error) and the index not be created rather than creating a unique 
index with duplicates (or whatever it is doing) causing subsequent queries to 
return incorrect results.

>On Wed, 30 Jul 2014 09:10:29 +0400
>Pavel Pimenov  wrote:
>
>> CREATE TABLE fly_hash_block(tth_id integer PRIMARY KEY NOT NULL, tth
>> number NOT NULL);
>> INSERT INTO fly_hash_block VALUES(1,1);
>> INSERT INTO fly_hash_block VALUES(2,2);
>> INSERT INTO fly_hash_block VALUES(3,2);
>> *CREATE UNIQUE INDEX iu_fly_hash_block_tth ON fly_hash_block(tth);*
>
>Not an answer if the table already exists, but a better solution would
>be to constrain the table:
>
>sqlite> CREATE TABLE fly_hash_block(
>   tth_id integer PRIMARY KEY NOT NULL,
>   tth number not null UNIQUE);
>sqlite> INSERT INTO fly_hash_block VALUES(1,1);
>sqlite> INSERT INTO fly_hash_block VALUES(2,2);
>sqlite> INSERT INTO fly_hash_block VALUES(3,2);
>Error: column tth is not unique
>sqlite>
>
>--jkl
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] sqlite bug after CREATE UNIQUE INDEX

[James K. Lowden]

On Wed, 30 Jul 2014 09:10:29 +0400
Pavel Pimenov  wrote:

> CREATE TABLE fly_hash_block(tth_id integer PRIMARY KEY NOT NULL, tth
> number NOT NULL);
> INSERT INTO fly_hash_block VALUES(1,1);
> INSERT INTO fly_hash_block VALUES(2,2);
> INSERT INTO fly_hash_block VALUES(3,2);
> *CREATE UNIQUE INDEX iu_fly_hash_block_tth ON fly_hash_block(tth);*

Not an answer if the table already exists, but a better solution would
be to constrain the table:

sqlite> CREATE TABLE fly_hash_block(
tth_id integer PRIMARY KEY NOT NULL, 
tth number not null UNIQUE);
sqlite> INSERT INTO fly_hash_block VALUES(1,1);
sqlite> INSERT INTO fly_hash_block VALUES(2,2);
sqlite> INSERT INTO fly_hash_block VALUES(3,2);
Error: column tth is not unique
sqlite> 

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

[sqlite] sqlite bug after CREATE UNIQUE INDEX

[Pavel Pimenov]

hi

C:\!dc-db\db-sqlite-corrupt\db-uniq-bug>sqlite3 --version
3.8.5 2014-06-04 14:06:34 b1ed4f2a34ba66c29b130f8d13e9092758019212


C:\!dc-db\db-sqlite-corrupt\db-uniq-bug>sqlite3.exe FlylinkDC.sqlite
0
SQL> CREATE TABLE fly_hash_block(tth_id integer PRIMARY KEY NOT NULL, tth
number NOT NULL);
Table created
SQL> INSERT INTO fly_hash_block VALUES(1,1);
1 row inserted
SQL> INSERT INTO fly_hash_block VALUES(2,2);
1 row inserted
SQL> INSERT INTO fly_hash_block VALUES(3,2);
1 row inserted
SQL> CREATE UNIQUE INDEX iu_fly_hash_block_tth ON fly_hash_block(tth);
CREATE UNIQUE INDEX iu_fly_hash_block_tth ON fly_hash_block(tth)
*ORA-01452: CREATE UNIQUE INDEX невозможно; найдены дублирующиеся ключи*
SQL> select * from fly_hash_block;
 TTH_IDTTH
--- --
  1  1
  2  2
  3  2
SQL> select distinct tth from fly_hash_block;
   TTH
--
 1
* 2*
SQL> select tth,count(*) from fly_hash_block group by tth;
   TTH   COUNT(*)
-- --
 1  1
 2  2


-- 
~PPA() {} //
http://flylinkdc.blogspot.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users