Greetings.
Consider the following:
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a text not null unique, b text not null unique);
sqlite> insert into t(a,b) values ('one','two');
sqlite> insert into t(a,b) values ('one','three');
Error: column a is not unique
sqlite>
Referring to <http://www.sqlite.org/lang_createtable.html>, I read:
> A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a
> single table may have any number of UNIQUE constraints. For each UNIQUE
> constraint on the table, each row must feature a unique combination of values
> in the columns identified by the UNIQUE constraint.
That reads very clearly to me that for each row in the table, the tuple (a,b)
(in this case) must be unique, but that the individual columns a and b need not
be distinct. That is, in the example above, the two inserted rows satisfy the
constraints because the tuples ('one','two') and ('one','three') are different.
Thus it appears that either SQLite 3.7.4 has a bug, or else this documentation
is confusing.
The phrase "For each UNIQUE constraint on the table" appears to be redundant;
if it's not, then that might need to be adjusted.
The wording that best matches what I take to be the meaning is simply "Each row
must feature a unique combination of values in the columns which contain UNIQUE
constraints"
I can creating the table via:
sqlite> create table t2(a text not null, b text not null, primary key (a,b));
This works, as does "unique (a,b)". This suggests the the documentation text
above is talking exclusively about table constraints, rather than column
constraints. That reading is now consistent, and makes the phrase "For each
UNIQUE constraint on the table" non-redundant, but it also means that there's
now no documented meaning to having UNIQUE constraints on columns, so that's an
alternative thing that might need clearing up.
I hope this is useful. If this is a bug, is this an adequare report of it? (I
can't see a public bugparade)
Best wishes,
Norman
--
Norman Gray : http://nxg.me.uk
Dept Physics and Astronomy, University of Glasgow, UK
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
