Thread: 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!