 |
|
Current group: pgsql.general
Re: Unique Index
| Dann Corbit | | Frank D. Engel, Jr. | | Stephan Szabo |
|
|
| | From: | Dann Corbit | | Subject: | Re: Unique Index | | Date: | Thu, 20 Jan 2005 10:55:50 -0800 |
|
|
 | 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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, January 20, 2005 8:03 AM
To: Greg Stark
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unique Index
Greg Stark writes:
> Tom Lane 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
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,
"", then the result of the predi-
cate> is true; otherwise, the result of the 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@postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
|
|
| | From: | Frank D. Engel, Jr. | | Subject: | Re: Unique Index | | Date: | Thu, 20 Jan 2005 14:26:43 -0500 |
|
|
| -----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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
> Sent: Thursday, January 20, 2005 8:03 AM
> To: Greg Stark
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Unique Index
>
> Greg Stark writes:
>> Tom Lane 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
>
> 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,
> "", then the result of the > predi-
> cate> is true; otherwise, the result of the > 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@postgresql.org
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
- -----------------------------------------------------------
Frank D. Engel, Jr.
$ 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@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
| | From: | Stephan Szabo | | Subject: | Re: Unique Index | | Date: | Thu, 20 Jan 2005 11:14:05 -0800 (PST) |
|
|
|
On Thu, 20 Jan 2005, Dann Corbit wrote:
> It is clear to me that only allowing a single null value will not
> violate the explanation below.
Given two rows in T with one column each
(NULL), (NULL)
Find two rows such that the value of each column in one row is non-null
and equal to the value of the corresponding column in the other row
according to 8.2. If there are no such rows the unique predicate returns
true (ie the constraint is satisfied).
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
|
|
| | |
|
 |