Re: 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'          <empty space>
> 'abc'          <empty space>

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
alban@magproductions.nl

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