Downey, Shawn wrote:
Does the following schema:
create table t(col1 text NOT NULL,
col2 text NOT NULL,
col3 text NOT NULL,
UNIQUE(col1,col2),
PRIMARY KEY(col1,col2));
State that the COMBINATION of col1+col2 must be unique? Or that BOTH
col1 and col2 must be unique? I assumed the first but I am getting for
rejections for "uniqueness constraint failed" using sqlite 2.8.0.
PRIMARY KEY implies UNIQUE and NOT NULL. So your specification is
redundant. Furthermore, a bug in SQLite causes multiple identical
indices to be created if you use both UNIQUE and PRIMARY KEY on
the same columns. You'll still get the right answers, but your
database file will be larger and updates will be slower.
The combination of col1+col2 must be unique. This works.
Example:
[EMAIL PROTECTED] drh]$ sqlite :memory:
SQLite version 2.8.15
Enter ".help" for instructions
sqlite> create table t(a,b,c,primary key(a,b));
sqlite> insert into t values(1,2,3);
sqlite> insert into t values(1,3,4);
sqlite> insert into t values(3,2,4);
sqlite> insert into t values(1,2,4);
SQL error: columns a, b are not unique
sqlite>
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
