INDEX and NULL

From: "Matthew Rudolph" <mrudolph(at)zetec(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: INDEX and NULL
Date: 2003-12-19 00:44:33
Message-ID: A0823E3FBDD5694493D6C61793F044BC22D1CC@exchange02.zetec.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Here are the sample tables for my question.

CREATE TABLE bar (
id int2 PRIMARY KEY UNIQUE DEFAULT nextval('bar_id_seq'),
...
);

CREATE TABLE foo (
id int2 PRIMARY KEY UNIQUE DEFAULT nextval('foo_id_seq'),
number int2 NOT NULL,
bar_id int2 REFERENCES bar ON UPDATE CASCADE ON DELETE CASCADE,
...
);
CREATE UNIQUE INDEX foo_number_id_index ON foo(number, bar_id);

I am a novice for sure. I am trying to prevent multiple combinations
of the number and bar_id fields. However, since the bar_id can be NULL
I am actually getting multiple combinations with NULL.

For example,
foo:
id number bar_id .....
-------------------------------
1 | 1 |
2 | 2 | 3
3 | 1 |
....

Row 1 and 3 are duplicates that I wish to disallow. I am however seeing
just this behavior with 7.3.2. I am seeing the duplicates. I am not able
to add another row with number = 2 and bar_id = 3.

What am I doing wrong? What sort of NULLism is biting me?

I have searched around the lists and the documentation, I am sure it is
explained but I am not able to see the solution. I link that helps out
would be appreciated.

Thanks for your help.

Matthew
-----------------------------------------------------------------------
DISCLAIMER: Information contained in this message and/or
attachment(s) may contain confidential information of Zetec, Inc.
If you have received this transmission in error, please notify
the sender by return email.
-----------------------------------------------------------------------

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Sergio L. Garza M. 2003-12-19 08:20:25 Upgrading from v7.1.3 to v7.4
Previous Message joseph speigle 2003-12-18 21:31:02 postgresql password manager release 1.0.0