-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Huh?
... the value of EACH COLUMN in one row is NOT NULL and IS EQUAL to ...
In order for values to be equal in SQL, neither one can be null. For this condition to hold, it is more than "clear" that at least one row must contain *NO* *NULL* *VALUES* (that means zero columns in that row may contain null values). Since *ALL* columns in the other row must be EQUAL to the corresponding column in that row, none of them can be null either. Therefore, the uniqueness predicate evaluates to false, and each of the two rows is considered unique compared to the other as soon as any null value shows up in either row.
There is *no* ambiguity here!
On Jan 20, 2005, at 1:55 PM, Dann Corbit wrote:
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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane Sent: Thursday, January 20, 2005 8:03 AM To: Greg Stark Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Unique Index
Greg Stark <gsstark@xxxxxxx> writes:Tom Lane <tgl@xxxxxxxxxxxxx> writes:constraints":Not for UNIQUE constraints. SQL92 section 4.10 "Integrity
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 columnsthat areall identical? Or does it mean no two rows have columns that excludinganynull 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@xxxxxxxxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
- ----------------------------------------------------------- Frank D. Engel, Jr. <fde101@xxxxxxxxxxx>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)
iD8DBQFB8AX07aqtWrR9cZoRAlu+AJ0YCv6VEID7MR5BHOf6rRl+gyPeLgCghImV Um7r0pyp2vfsCVArSOkswMM= =kzpI -----END PGP SIGNATURE-----
___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
