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