The following bug has been logged online:
Bug reference: 3276
Logged by: michael
Email address: miblogic@yahoo.com
PostgreSQL version: 8.2.4
Operating system: winxp
Description: unique index/unique constraint
Details:
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
so??
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,
'INCOME'
while mssql unique constraint, doesn't allowed duplicate NULL + INCOME
ALTER TABLE account_category ADD CONSTRAINT uk_account_category
UNIQUE(sub_account_of,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
(upper(account_category::text))
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));
