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

Reply via email to