On Sun, Sep 11, 2011 at 02:00:48PM +0200, Bernd Lehmkuhl scratched on the wall:
> Am 11.09.2011 13:42, schrieb liviodl:
> >
> >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.
> I think that the 'OR REPLACE' clause refers to the primary key,
No, it will trigger on any UNIQUE constraint violation.
My guess is that one of the individual columns has a UNIQUE constraint.
Is "id" or one of the other columns defined as a PRIMARY KEY?
-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
