From: | Matthew Wilson <matt(at)tplus1(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | NULL values seem to short-circuit my unique index |
Date: | 2008-09-29 02:32:56 |
Message-ID: | slrnge0fio.b3l.matt@sprout.tplus1.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Chris | 2008-09-29 02:59:59 | Re: NULL values seem to short-circuit my unique index |
Previous Message | Seb | 2008-09-29 00:31:52 | Re: inserting to a multi-table view |