NULL values seem to short-circuit my unique index
I'm trying to comprehend how NULL values interact with unique indexes.
It seems like I can insert two rows with NULL values in a column with a
unique constraint just fine.
Is there something special about NULL? Can anyone post some links to
explain what is going on?
Here's the example that stumped me:
I created an organization table and a category table:
matt=# \d organization
Table "public.organization"
Column | Type | Modifiers
--------+---------+----------------------------------
id | integer | not null default
nextval('organization_id_seq'::regclass)
name | text |
Indexes:
"organization_pkey" PRIMARY KEY, btree (id)
matt=# \d category
Table "public.category"
Column | Type | Modifiers
--------------------+---------+-------------------------------------------------------
organization_id | integer |
id | integer | not null default nextval('category_id_seq'::regclass)
name | text |
parent_category_id | integer |
Indexes:
"category_pkey" PRIMARY KEY, btree (id)
"nodup_categories" UNIQUE, btree (organization_id, name, parent_category_id)
Foreign-key constraints:
"category_organization_id_fkey" FOREIGN KEY (organization_id) REFERENCES organization(id)
"category_parent_category_id_fkey" FOREIGN KEY (parent_category_id) REFERENCES category(id)
I thought that nodup_categories index would prevent me from putting in
these values, but I was wrong:
matt=# insert into category (organization_id, name) values (1, 'bogus');
INSERT 0 1
matt=# insert into category (organization_id, name) values (1, 'bogus');
INSERT 0 1
matt=# insert into category (organization_id, name) values (1, 'bogus');
INSERT 0 1
matt=# select * from category;
organization_id | id | name | parent_category_id
-----------------+----+-------+--------------------
1 | 1 | bogus |
1 | 2 | bogus |
1 | 3 | bogus |
(3 rows)
So, obviously there's something I'm missing. Clearly an index exists.
What's the deal with NULL?
I think I'll use some other value besides NULL to indicate categories
with parents. Then I would need to figure out how to handle the FK
constraint on the parent_category_id column.
Matt
Matthew Wilson wrote:
I'm trying to comprehend how NULL values interact with unique indexes.
It seems like I can insert two rows with NULL values in a column with a
unique constraint just fine.Is there something special about NULL? Can anyone post some links to
explain what is going on?
When you think of null as "unknown", it makes sense.
Does an unknown value equal another unknown value?
http://www.postgresql.org/docs/8.3/interactive/functions-logical.html
http://www.postgresql.org/docs/8.3/interactive/functions-comparison.html
--
Postgresql & php tutorials
http://www.designmagick.com/
Matthew Wilson <matt@tplus1.com> writes:
It seems like I can insert two rows with NULL values in a column with a
unique constraint just fine.
This is per SQL spec and quite well-documented in our manual ...
regards, tom lane
Matthew Wilson wrote:
I'm trying to comprehend how NULL values interact with unique indexes.
It seems like I can insert two rows with NULL values in a column with a
unique constraint just fine.Is there something special about NULL? Can anyone post some links to
explain what is going on?
http://www.postgresql.org/docs/8.3/interactive/ddl-constraints.html#AEN2058
Last paragraph just above 5.3.4.
What's the deal with NULL?
NULL = NULL is not true, it's null
NULL <> NULL is not false, it's null
It's the normal SQL 3 state logic (true/false/null) with only the true
value from the comparison causing the constraint violation. Think of
the unique constraint check like "does this value equal any other value
already recorded".
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au
You can always add a constraint that these columns cannot be NULL
themselves. That removes this problem.
On Sep 28, 2008, at 11:17 PM, Klint Gore <kgore4@une.edu.au> wrote:
Show quoted text
Matthew Wilson wrote:
I'm trying to comprehend how NULL values interact with unique
indexes.
It seems like I can insert two rows with NULL values in a column
with a
unique constraint just fine.Is there something special about NULL? Can anyone post some links to
explain what is going on?http://www.postgresql.org/docs/8.3/interactive/ddl-constraints.html#AEN2058
Last paragraph just above 5.3.4.What's the deal with NULL?
NULL = NULL is not true, it's null
NULL <> NULL is not false, it's nullIt's the normal SQL 3 state logic (true/false/null) with only the
true value from the comparison causing the constraint violation.
Think of the unique constraint check like "does this value equal any
other value already recorded".klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350Ph: 02 6773 3789 Fax: 02 6773 3266
EMail: kgore4@une.edu.au--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sep 29, 2008, at 4:59 AM, Chris wrote:
Matthew Wilson wrote:
I'm trying to comprehend how NULL values interact with unique
indexes.
It seems like I can insert two rows with NULL values in a column
with a
unique constraint just fine.
Is there something special about NULL? Can anyone post some links to
explain what is going on?When you think of null as "unknown", it makes sense.
Does an unknown value equal another unknown value?
Also, you wouldn't be able to put a UNIQUE constraint on foreign keys
with a 0..1 to 1 relation if two NULL values would be considered not
unique. That UNIQUE constraint is what makes it a 0..1 to 1 relation
(as would a PRIMARY KEY constraint). Without it it would be a * to 1
relation.
If two NULLs would be considered not unique, only one NULL key
reference would be allowed and all following ones would result in a
unique constraint violation!
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,49157dd89507271520953!