Igor and Drake, hello. On 2010 Dec 26, at 19:36, Igor Tandetnik wrote:
> 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. Yes, I understand it _now_. Documentation is always transparent, once one understands what's being documented. Documentation is very hard to write, made more complicated by the fact that we only get one chance at reading something for the first time. Drake remarked: > That is, unless I am very much mistaken, > > CREATE TABLE t (a ... UNIQUE) > > and > > CREATE TABLE t (a ..., UNIQUE (a)) > > are exactly equivalent and the former is an abbreviation for the > latter. Aha: this is a key point. In order to successfully parse the paragraph, the reader has to be aware that a column constraint and a table constraint are importantly different things, even though they look very similar to each other, to someone not steeped in SQL. Once you know the relationship between them (indeed, once you understand one is syntactic sugar for the other), then the paragraph is transparent. The semi-naive reader could use a little more spelling out, though. Even one extra sentence would help clarify things, if it said: "Note that a column constraint 'a ... unique' is equivalent to a single-column table constraint 'a ..., unique(a)'". That helpfully leads the reader to the right place. Both quoted: > Each table in SQLite may have at most one PRIMARY KEY. If the keywords > PRIMARY KEY are added to a column definition, then the primary key for the > table consists of that single column. Or, if a PRIMARY KEY clause is > specified as a table-constraint, then the primary key of the table consists > of the list of columns specified as part of the PRIMARY KEY clause. > > A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a > single table may have any number of UNIQUE constraints. Hmm: it is not natural to parse this so that it is referring to UNIQUE as a column constraint, though I now see how you can manage that (it mentions PRIMARY KEY within a column definition, and then _in the next paragraph_, with a different thought in between, it talks of a UNIQUE constraint being like a PRIMARY KEY one -- is that what you mean?). That only makes sense _after_ you understand it. Just to be clear, I'll stress that the SQLite documentation is very good, and agreeably compact. This particular aspect may be a little over-compressed, though in a way which is pretty straightforward to fix. 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
