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

I am not sure how this can happen

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

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

Depends on what you mean by <empty space>. 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   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.