>OK, at the risk of exposing my lack of edification, I'm wondering if >someone can explain why this simple test of unique column constraints >doesn't work. At least it doesn't work as I expected it would (i.e. >that >the second insert would yield a unique constraint violation). > >create table t (k integer primary key, s text UNIQUE); >insert into t (s) values ("foo"); >insert into t (s) values ("bar"); >select * from t; >k s >---------- ---------- >1 foo >2 bar
Other than the fact you happen to be a lucky duck hunter not getting yourself shot in the ass for using the wrong quotes, what is the problem? Why are you confused? INTEGER PRIMARY KEY is the record number in the table. Each record can only have one record number. Each record number is unique. The first is number one, the second is number 2, and so on and so forth. >Adding to my confusion is this: >insert into t values (3, "bar"); >Error: UNIQUE constraint failed: t.s > Any why should this be "confusing"? Seems perfectly obvious to me. You have said t.s should be unique. You attempted to insert a duplicate value of t.s. You were told that the UNIQUE containt t.s failed. The fact that this is "record number three" is irrelevant -- just as "the sun is shining today" is irrelevant. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users