On 2018/05/16 1:25 AM, Mark Wagner wrote:
OK, at the risk of exposing my lack of edification...
Sometimes you pay the price for taking a risk. :)
create table t (k integer primary key, s text UNIQUE);
Did you by any chance assume the "UNIQUE" constraint to cover both the
fields k and s? It applies of course only to s here. If you wanted it to
apply to both, the schema should read:
create table t(k integer primary key, s text, unique(k,s) );
(I'm just trying to guess at what caused the confusion - even if you do
change to this schema, the uniqueness constraint will pass even better
than before, since the primary key is by definition unique and
automatically increments, so any key in which it appears MUST also be
unique for every value in it, even if you added 'bar' ten times in a
row, it will never fail.)
insert into t (s) values ("foo");
insert into t (s) values ("bar");
select * from t;
k s
---------- ----------
1 foo
2 bar
foo and bar here (which should be quoted like 'foo' and 'bar' by the
way, double-quotes are for identifiers) are very unique, so cannot
possibly constitute a uniqueness violation.
Adding to my confusion is this:
insert into t values (3, "bar");
Error: UNIQUE constraint failed: t.s
whereas 'bar' here definitely is not unique (there is already a 'bar' in
there) and thus fails.
I've always found the higher level languages to be slightly less than
intuitive, since they try to "read like English", except the language is
nuanced and often what something seems to mean in English is not what it
really means in the computed sense. That means that a statement fragment
like UNIQUE needs as much documentation as, for instance, the lower
level assembly language MOV command, if not more, because MOV left you
with zero assumptions, you had to check the docs to see what it did, and
even then, it delightfully did the very minimum.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
