Re: [sqlite] create table 'unique' constraints behave unexpectedly

On 27 Dec 2010, at 1:17am, Norman Gray wrote: > It's true there are many 'SQL for dummies' pages around the web, but none of > them, as far as I've found, are any good. They tend to be prolix, vague, and > written, well..., for dummies. We need a competing series. Trouble is "... for reasona

Re: [sqlite] create table 'unique' constraints behave unexpectedly

Simon and Drake, hello. On 2010 Dec 26, at 23:49, Simon Slavin wrote: > On 26 Dec 2010, at 10:22pm, Drake Wilson wrote: > >> I think this is part of the disconnect. I don't get the feeling that >> the SQLite documentation is intended for people who have little or no >> SQL background, but rath

Re: [sqlite] create table 'unique' constraints behave unexpectedly

Quoth Simon Slavin , on 2010-12-27 00:17:37 +: > The distinction between column constraints and table constraints is > part of the spec of SQL. Had you read the SQL spec first, you > wouldn't have started this thread. But nobody expects you to read > the SQL spec before using SQL. Except tha

Re: [sqlite] create table 'unique' constraints behave unexpectedly

On 27 Dec 2010, at 12:01am, Drake Wilson wrote: > Quoth Simon Slavin , on 2010-12-26 23:49:50 +: >> On the other hand, almost nobody who uses SQL has read the >> specification document for SQL. And frankly none of them should >> have to: it's easier to understand SQL than the specification f

Re: [sqlite] create table 'unique' constraints behave unexpectedly

Quoth Simon Slavin , on 2010-12-26 23:49:50 +: > On the other hand, almost nobody who uses SQL has read the > specification document for SQL. And frankly none of them should > have to: it's easier to understand SQL than the specification for > SQL. So using a technical term that exists in the

Re: [sqlite] create table 'unique' constraints behave unexpectedly

On 26 Dec 2010, at 10:22pm, Drake Wilson wrote: > I think this is part of the disconnect. I don't get the feeling that > the SQLite documentation is intended for people who have little or no > SQL background, but rather as a reference for those who are already > familiar with SQL relational data

Re: [sqlite] create table 'unique' constraints behave unexpectedly

Quoth Norman Gray , on 2010-12-26 22:02:39 +: > 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 someon

Re: [sqlite] create table 'unique' constraints behave unexpectedly

Igor and Drake, hello. On 2010 Dec 26, at 19:36, Igor Tandetnik wrote: > Norman Gray wrote: >> sqlite> create table t(a text not null unique, b text not null unique); >> >> Referring to , I read: >> >>> A UNIQUE constraint is similar to a PRIMARY K

Re: [sqlite] create table 'unique' constraints behave unexpectedly

Quoth Igor Tandetnik , on 2010-12-26 14:39:15 -0500: [quoting the documentation] > 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 claus

Re: [sqlite] create table 'unique' constraints behave unexpectedly

Quoth Norman Gray , on 2010-12-26 19:11:02 +: > > A UNIQUE specifier on a column creates one UNIQUE constraint on > > exactly that column. UNIQUE specifiers on multiple columns are > > unrelated. You may specify a multi-column UNIQUE constraint by > > declaring it separately (and not as part

Re: [sqlite] create table 'unique' constraints behave unexpectedly

Norman Gray wrote: >It appears that column UNIQUE constraints are not discussed on that page. Are too: 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

Re: [sqlite] create table 'unique' constraints behave unexpectedly

Norman Gray wrote: > sqlite> create table t(a text not null unique, b text not null unique); > > Referring to , I read: > >> A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a >> single table may have any number of UNIQUE >> con

Re: [sqlite] create table 'unique' constraints behave unexpectedly

Drake, hello. On 2010 Dec 26, at 18:26, Drake Wilson wrote: > Quoth Norman Gray , on 2010-12-26 18:14:23 +: >> The phrase "For each UNIQUE constraint on the table" appears to be >> redundant; if it's not, then that might need to be adjusted. > > A UNIQUE specifier on a column creates one UN

Re: [sqlite] create table 'unique' constraints behave unexpectedly

Quoth Simon Slavin , on 2010-12-26 18:28:28 +: > ... and to do that you create an index on that tuple, and require the index > to enforce uniqueness: > > CREATE UNIQUE INDEX tab ON t (a,b) If you like. I was referring to CREATE TABLE t (a, b, UNIQUE (a, b)). ---> Drake Wilson __

Re: [sqlite] create table 'unique' constraints behave unexpectedly

On 26 Dec 2010, at 6:26pm, Drake Wilson wrote: > Quoth Norman Gray , on 2010-12-26 18:14:23 +: >> The phrase "For each UNIQUE constraint on the table" appears to be >> redundant; if it's not, then that might need to be adjusted. > > A UNIQUE specifier on a column creates one UNIQUE constrain

Re: [sqlite] create table 'unique' constraints behave unexpectedly

Quoth Norman Gray , on 2010-12-26 18:14:23 +: > The phrase "For each UNIQUE constraint on the table" appears to be > redundant; if it's not, then that might need to be adjusted. A UNIQUE specifier on a column creates one UNIQUE constraint on exactly that column. UNIQUE specifiers on multiple