Re: [sqlite] Multi-column unique constraint in SQLite

[Bernd Lehmkuhl]

Am 11.09.2011 13:42, schrieb liviodl:

Hi guys,

I'm trying to create a multi-column unique constraint in SQLite, but I don't
have success. In table "players", I've created the following index:

     CREATE UNIQUE INDEX "players_unique" ON "players" ("id" ASC, "skill"
ASC, "stagione" ASC, "settimana" ASC)

When I issue the following commands, I see that a single row is create and
then updated, even if I was hoping to obtain two different rows:

     INSERT OR REPLACE INTO "players"
(id,skill,e,f,s,r,a,g,p,w,c,stagione,settimana,v) VALUES
("100","187","1","1","1","1","50","0","1","1","1","20","1","2011-09-05");

     INSERT OR REPLACE INTO "players"
(id,skill,e,f,s,r,a,g,p,w,c,stagione,settimana,v) VALUES
("100","187","1","1","1","1","50","0","1","1","1","20","2","2011-09-05");

The two INSERT rows are identical except the value under "settimana". I
expected to not have a conflict, so that an INSERT should be performed for
both the commands, but at the end I have only one row with settimana=2.

Do you know why?

Thanks in advance,

Livio

I think that the 'OR REPLACE' clause refers to the primary key, which 
hasn't necessarily to do with a unique index defined elsewhere. So if 
you have a primary key set on id, that would be the result.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users