Re: [GENERAL] unique key issue
On Mon, Sep 18, 2006 at 03:54:05PM -0700, Junkone wrote: > HI > I have a table with a unique key constraint on col1,col2. The dattabase > allowed an multiple inserts. here is how > > col1 col2 > 'abc' > 'abc' Depends on what you mean by . If you mean NULL, then it's according to the SQL standard. NULL <> NULL so those rows are not equal. If you mean some real value, then yes, that's wierd. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] unique key issue
Junkone wrote: HI I have a table with a unique key constraint on col1,col2. The dattabase allowed an multiple inserts. here is how col1 col2 'abc' 'abc' I suppose the values in col2 are NULL values? You cannot compare NULL values. NULL == NULL evaluates to NULL (not true or false), you have no way of knowing those two records are equal. PostgreSQL (among others) assumes NULL values to always be different. The meaning and interpretation of NULL is a frequent topic of discussion. I am not sure how this can happen You can solve your problem by creating 2 unique constraints: CREATE UNIQUE INDEX idx1 ON table (col1, col2) WHERE col2 IS NOT NULL; CREATE UNIQUE INDEX idx2 ON table (col1) WHERE col2 IS NULL; Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] unique key issue
HI I have a table with a unique key constraint on col1,col2. The dattabase allowed an multiple inserts. here is how col1 col2 'abc' 'abc' I am not sure how this can happen ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
