Обсуждение: 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: > 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 > > > -- > 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!