From: | "Matthew Rudolph" <mrudolph(at)zetec(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | INDEX and NULL values |
Date: | 2003-12-19 00:23:21 |
Message-ID: | A0823E3FBDD5694493D6C61793F044BC22D1CA@exchange02.zetec.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
-----------------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Dok, D. van | 2003-12-19 14:41:04 | Crosstable query |
Previous Message | A E | 2003-12-18 18:20:16 | Returning Setof Record Dynamically |