Re: Unique Index

It is clear to me that only allowing a single null value will not
violate the explanation below.

It would be equally true that allowing multiple null values would not
violate it.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Thursday, January 20, 2005 8:03 AM
To: Greg Stark
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Unique Index

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Not for UNIQUE constraints. SQL92 section 4.10 "Integrity
constraints":
>>
>> A unique constraint is satisfied if and only if no two rows in
>> a table have the same non-null values in the unique columns.

> That's ambiguous. Does it mean no two rows have all non-null columns
that are
> all identical? Or does it mean no two rows have columns that excluding
any
> null columns are identical.

OK, try the more formal definition in 8.9 <unique predicate>

2) If there are no two rows in T such that the value of each
column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause
8.2,
"<comparison predicate>", then the result of the <unique
predi-
cate> is true; otherwise, the result of the <unique
predicate>
is false.

(11.7 defines the UNIQUE constraint in terms of the unique predicate)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org