On 3/27/2014 4:21 AM, Richard Hipp wrote:
On Thu, Mar 27, 2014 at 12:49 AM, SongbookDB
<shaun_thom...@songbookdb.com>wrote:
Gidday guys
I'm working with SQLite in Flash.
I have this unique index:
CREATE UNIQUE INDEX songsIndex ON songs ( DiscID, Artist, Title )
I have a parametised recursive functionset up to insert any new rows
(single or multiple).
It works fine if I try to insert a row with the same DiscID, Artist and
Title as an existing row - ie it ignores inserting the existing row, and
tells me that 0 out of 1 records were updated - GOOD.
However, if, for example the DiscId is blank, but the artist and title are
not, a new record is created when there is already one with a blank DiscId
and the same artist and title - BAD.
NULL values are distinct for the purpose of computing uniqueness. This is
how PostgreSQL, Oracle, and MySQL work. MS-SQL works differently. The SQL
standards are ambiguous on this point. See
http://www.sqlite.org/nulls.html for additional information.
This isn't exactly how Oracle works. It's true for when there is a mix
of NULL and not NULL values for a unique constraint, but it is not true
if all fields in the unique constraint are null -- you can have any
number of rows like that. See the discussion here:
https://community.oracle.com/message/1255458 or Oracle documentation
here
http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm#i1034458.
Peter
I traced out the disc id prior to the insert, and Flash is telling me it's
undefined. So I've coded it to set anything undefined to "" (an empty
string) to make sure it's truly an empty string being inserted - but
subsequent inserts still ignore the unique index and add a brand new row
even though the same row exists.
Empty strings are not distinct for the distinct, so setting DiscID to ''
instead of NULL ought to work. What is the declared datatype of DiscID?
Perhaps DiscID is a numeric type, and Flash is trying to be helpful and
convert the empty string into a NULL for you automatically due to the type
mismatch?
What am I misunderstanding?
Thanks for your time and help.
--
Shaun Thomson
Owner - SongbookDB
_______________________________________________
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
