> CREATE TABLE User ( Name VARCHAR (40),
> UID INTEGER,
> DeviceID VARCHAR (64) DEFAULT 'Unknown',
> PRIMARY KEY (Name, UID, DeviceID) );
> INSERT INTO User (Name,DeviceID) VALUES ('Michael','Test');
> INSERT INTO User (Name,DeviceID) VALUES ('Michael','Test');
>
> Gets me two rows with ('Michael',NULLm'Test').
>
> I think that 3 is a bug.
D.R.Hipp:
"I concur. The uniqueness constraint is not being enforced for
multi-column primary keys. Why don't you write a ticket."
-------------------------------------------------------------------
[1]If you're intending to follow SQL92, this is definitely
a bug, since the std does not allow any column of a primary
key to be null. Both insertions should have been rejected,
not just the second.
[2]It's not true in general (in my experience with SQLite)
that "The uniqueness constraint is not being enforced for
multi-column primary keys". The failure occurs only if you
allow nulls and consider all nulls to be identical (i.e. not
distinct).
[3]If you're going to allow nulls in a PK, I think that the
current behavior is 'correct' -- nulls should be considered
distinct in this context (if you allow them, contrary to [1],
above).
Regards
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
