On Thu, Mar 27, 2014 at 12:49 AM, SongbookDB <[email protected]>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.htmlfor additional information. > > 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
