Norman Gray <nor...@astro.gla.ac.uk> wrote: > sqlite> create table t(a text not null unique, b text not null unique); > > 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
How so? It says, "for each UNIQUE constraint on the table". There are two unique constaints on your table: the subsequent rule applies to each one separately. So, values of "a" must be unique across all rows; independently, values of "b" must be unique across all rows. > Thus it appears that either SQLite 3.7.4 has a bug, or else this > documentation is confusing. The documentation looks clear to me as written. In fact, it's difficult for me to see how you managed to come up with your interpretation of it. > The phrase "For each UNIQUE constraint on the table" appears to be redundant Well, if you pick and choose which parts of the documentation to take at face value and which parts to ignore, then yes, I can see how it can quickly become confusing. > if it's not, then that might need to be adjusted. In what way? Which part of this phrase is unclear? > 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" However, what you take to be the meaning doesn't match reality, which renders your wording proposal moot. > 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. The documentation does too describe both column and table constraints. Your original statement is equivalent to create table t(a text not null, b text not null, unique(a), unique(b)); > That reading is now consistent, and makes the phrase "For each UNIQUE > constraint on > the table" non-redundant That phrase was not redundant to begin with. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
