Skip site navigation (1) Skip section navigation (2)

BUG #3276: unique index/unique constraint

From: "michael" <miblogic(at)yahoo(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3276: unique index/unique constraint
Date: 2007-05-14 07:27:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3276
Logged by:          michael
Email address:      miblogic(at)yahoo(dot)com
PostgreSQL version: 8.2.4
Operating system:   winxp
Description:        unique index/unique constraint

from postgresql irc, one says:

the docs say: "When an index is declared unique, multiple table rows with
equal indexed values will not be allowed. Null values are not considered
equal. A multicolumn unique index will only reject cases where all of the
indexed columns are equal in two rows."

my program's case:

CREATE TABLE account_category
account_category_id varchar(38), -- guid, not nullable, primary key

sub_account_of varchar(38), 
/* guid, nullable, links to account_category_id if the account_category is
sub-level of another account_category, makes the table tree-structured */

account_category varchar(50), -- not nullable, e.g.  INCOME, Bank Interest,
Union Bank, China Bank, Commission

account_category_full_description varhar(509), -- could allowed ten levels
of recursion :-)
/* quickbooks' like data structure, chained by trigger, e.g. INCOME,
INCOME:Bank Interest, INCOME:Bank 
Interest:Union Bank, INCOME:Bank Interest:China Bank, INCOME:Commission */


creating unique key in sub_account_of+account_category:

CREATE UNIQUE INDEX uk_account_category
  ON account_category
  USING btree
  (sub_account_of, upper(account_category::text));

-- allowed
insert into account_category(account_category_id, sub_account_of,
account_category) SELECT newid(), NULL, 'INCOME'; 

-- second insert of same values allowed.  because the SQL standard says
insert into account_category(account_category_id, sub_account_of,
account_category) SELECT newid(), NULL, 'INCOME'; 

postgresql and mysql, behaves in same way. i.e. allow two rows of NULL,

while mssql unique constraint, doesn't allowed duplicate NULL + INCOME

ALTER TABLE account_category ADD CONSTRAINT uk_account_category

this IMHO, mssql breaks the standard with fashion :-)

with regards to unique rows, i think we should deviate from the sql
standard.  or if this isn't possible, we should at least document this in
postgresql's unique index/unique constraint's gotchas, make more obvious. 
i'm really surprised that two rows with equal values is allowed

btw, thanks to davidfetter and oicu for suggesting partial index for my
program's "unique" case :-)

CREATE UNIQUE INDEX uk_account_category_topmost
  ON account_category
  USING btree
  WHERE sub_account_of IS NULL;

CREATE UNIQUE INDEX uk_account_category_sublevel
  ON account_category
  USING btree
  (sub_account_of, upper(account_category::text));


pgsql-bugs by date

Next:From: Nilay CeterDate: 2007-05-14 08:30:17
Subject: BUG #3277: error occurs between different versions
Previous:From: Pavel StehuleDate: 2007-05-13 13:24:28
Subject: pg_standby (wrong parametr -u in help note

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group