> 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]