From: David Allardyce <dave@pod13.net>
To: pgsql-sql@postgresql.org
Subject: Re: Table Constraints with NULL values
Date: Fri, 19 Oct 2001 22:14:29 -0400
Message-ID: <045b01c1590c$f3751220$6100a8c0@pod13.com> (raw)
References: <026601c15849$b4f38680$6100a8c0@pod13.com>
<11522.1003474419@sss.pgh.pa.us>
I don't mean to re-hash an argument that has been debated-to-death before,
but I can't help myself...
> > However, shouldn't any values that are not NULL violate the constraint
if
> > the same values exist already?
>
> No. Postgres is conforming to the SQL standard in this. SQL92 saith
> in section 4.10:
>
> 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. In
> addition, if the unique constraint was defined with PRIMARY KEY,
> then it requires that none of the values in the specified column
or
> columns be the null value.
I am not sure the standard supports your statement. Apparently only the
PRIMARY KEY constraint may not contain null values, otherwise its a
violation if "..two rows... have the same non-null values..."
> There's a slightly different statement in the definition of the UNIQUE
> predicate, section 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,
> "<comparison predicate>", then the result of the <unique
predi-
> cate> is true; otherwise, the result of the <unique predicate>
> is false.
>
> AFAICT the intent is that all the comparison columns must be non-null
> (and equal) for two rows to be considered to violate the uniqueness
> constraint.
If 'each' means 'every' then I can see how you come to that conclusion.
However, AFAICT the intent is to say that all rows in a table can be said to
be unique if none of the non-null columns contains equal values in the
corresponding column of another row. If I use my definition then two all
null rows will not violate the constraint. If section 8.9 says that every
column must be NON-NULL, then any row that contains a NULL column will
always be in violation of uniquness.
> The standard *does* use the concept that you are after: section
> 3.1 Definitions saith
>
> h) distinct: Two values are said to be not distinct if either:
> both are the null value, or they compare equal according to
> Subclause 8.2, "<comparison predicate>". Otherwise they are
> distinct. Two rows (or partial rows) are distinct if at least
> one of their pairs of respective values is distinct. Otherwise
> they are not distinct. The result of evaluating whether or not
> two values or two rows are distinct is never unknown.
>
> i) duplicate: Two or more values or rows are said to be
duplicates
> (of each other) if and only if they are not distinct.
>
> which is terminology that they carefully refrain from using in defining
> uniqueness constraints. Had they meant what you want the behavior to
> be, ISTM they'd have defined uniqueness constraints by saying "all the
> rows must be distinct".
>
Actually, I don't think the definition of distinct is a contradiction at
all. Instead, I think it explains why they specifically exclude NULL values
from the uniqueness comparison. If they had defined uniqueness as "all rows
must be distinct" then two all null rows would violate the uniqueness
constraint. Not the behavior I want at all.
David Allardyce
-----------------
ISTM is the only one I couldn't figure out.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dave@pod13.net
Subject: Re: Table Constraints with NULL values
In-Reply-To: <045b01c1590c$f3751220$6100a8c0@pod13.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox