Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

[Simon Slavin]

On 5 Oct 2010, at 9:40pm, Paweł Salawa wrote:

>> Maybe you should remove the "ON CONFLICT IGNORE" clause at the end of the
>> uniqueness constraint...
> 
> You're right, of course.
> Shame on me for missing it :( Shame on me!

We all make mistakes like that.  It's really useful to have someone else look 
at your code and ask "Hey, are you doing this for some wonderful reason I don't 
understand ?".

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

Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

[Paweł Salawa]

> Maybe you should remove the "ON CONFLICT IGNORE" clause at the end of the
> uniqueness constraint...

You're right, of course.
Shame on me for missing it :( Shame on me!

-- 
Paweł Salawa
pawelsal...@gmail.com

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

Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

[Jay A. Kreibich]

On Tue, Oct 05, 2010 at 09:56:52PM +0200, Pawe?? Salawa scratched on the wall:
> Hi,
> 
> My SQLite is 3.7.2.
> 
> I have a table like this:
> 
> CREATE TABLE [newsd] (
>   [id] INTEGER PRIMARY KEY AUTOINCREMENT,
>   [date] INTEGER NOT NULL, [title] TEXT NOT NULL,
>   [yhfgdfhd] NONE,
>   CONSTRAINT "fg" UNIQUE ([yhfgdfhd]) ON CONFLICT IGNORE
> )

> So I'm indeed unable to set duplicate on UNIQUE column, but SQLite doesn't 
> raise error, thus my application doesn't call ROLLBACK for that case.

  No error is raised because you told SQLite to ignore the error 
  with "ON CONFLICT IGNORE."  

  

  IGNORE

When an applicable constraint violation occurs, the IGNORE
resolution algorithm skips the one row that contains the
constraint violation and continues processing subsequent rows
of the SQL statement as if nothing went wrong. Other rows
before and after the row that contained the constraint
violation are inserted or updated normally. No error is
returned when the IGNORE conflict resolution algorithm is used.


  In specific, note the last sentence in that paragraph.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

[Richard Hipp]

On Tue, Oct 5, 2010 at 3:56 PM, Paweł Salawa  wrote:

> Hi,
>
> My SQLite is 3.7.2.
>
> I have a table like this:
>
> CREATE TABLE [newsd] (
>  [id] INTEGER PRIMARY KEY AUTOINCREMENT,
>  [date] INTEGER NOT NULL, [title] TEXT NOT NULL,
>  [yhfgdfhd] NONE,
>  CONSTRAINT "fg" UNIQUE ([yhfgdfhd]) ON CONFLICT IGNORE
> )
>
> so column [yhfgdfhd] is UNIQUE, and [title] is NOT NULL.
> Now follow the log of 'sqlite3' calls:
>
> $ sqlite3 "data.db"
> SQLite version 3.7.2
> Enter ".help" for instructions
> sqlite> select * from newsd where ROWID = 16;
> 16|12.2.12||e
> sqlite> select * from newsd where ROWID = 21;
> 21|||x
> sqlite> UPDATE [newsd] SET [yhfgdfhd] = 'e' WHERE ROWID = 21;
> sqlite> select * from newsd where ROWID = 21;
> 21|||x
> sqlite> UPDATE [newsd] SET [title] = NULL WHERE ROWID = 21;
> SQL error: newsd.title may not be NULL
>
> So I'm indeed unable to set duplicate on UNIQUE column, but SQLite doesn't
> raise error, thus my application doesn't call ROLLBACK for that case.
>
> Last call at the end is to ensure that other constraint violation raises
> error
> correctly.
>

Maybe you should remove the "ON CONFLICT IGNORE" clause at the end of the
uniqueness constraint...



>
> Same behaviour takes place for tclsqlite extension.
>
> Regards,
> --
> Paweł Salawa
> pawelsal...@gmail.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

[Paweł Salawa]

Hi,

My SQLite is 3.7.2.

I have a table like this:

CREATE TABLE [newsd] (
  [id] INTEGER PRIMARY KEY AUTOINCREMENT,
  [date] INTEGER NOT NULL, [title] TEXT NOT NULL,
  [yhfgdfhd] NONE,
  CONSTRAINT "fg" UNIQUE ([yhfgdfhd]) ON CONFLICT IGNORE
)

so column [yhfgdfhd] is UNIQUE, and [title] is NOT NULL.
Now follow the log of 'sqlite3' calls:

$ sqlite3 "data.db"
SQLite version 3.7.2
Enter ".help" for instructions
sqlite> select * from newsd where ROWID = 16;
16|12.2.12||e
sqlite> select * from newsd where ROWID = 21;
21|||x
sqlite> UPDATE [newsd] SET [yhfgdfhd] = 'e' WHERE ROWID = 21;
sqlite> select * from newsd where ROWID = 21;
21|||x
sqlite> UPDATE [newsd] SET [title] = NULL WHERE ROWID = 21;
SQL error: newsd.title may not be NULL

So I'm indeed unable to set duplicate on UNIQUE column, but SQLite doesn't 
raise error, thus my application doesn't call ROLLBACK for that case.

Last call at the end is to ensure that other constraint violation raises error 
correctly.

Same behaviour takes place for tclsqlite extension.

Regards,
-- 
Paweł Salawa
pawelsal...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users