On 27/03/13 06:14, Darren Duncan wrote:
On 2013.03.26 1:40 AM, Albe Laurenz wrote:
Darren Duncan wrote:
So, determining if 2 rows are the same involves an iteration of
dyadic logical
AND over the predicates for each column comparison. Now logical AND
has an
identity value, which is TRUE, because "TRUE AND p" (and "p AND
TRUE") results
in "p" for all "p". Therefore, any 2 rows with zero columns each
are the same.
Since any 2 rows with zero columns are the same, the "UNIQUE
predicate" is FALSE
any time there is more than 1 row in a table.
Does anyone agree or disagree with this logic?
Yes :^)
You could use the same kind of argument like this:
UNIQUE is true iff any two rows in T satisfy for each column:
the column in row 1 is null OR the column in row 2 is null OR
the column in row 1 is distinct from the column in row 2
Now you you iterate your logical AND over this predicate
for all columns and come up with TRUE since there are none.
Consequently UNIQUE is satisfied, no matter how many rows there are.
In a nutshell:
All members of the empty set satisfy p, but also:
all members of the empty set satisfy the negation of p.
You can use this technique to make anything plausible.
Consider the context however. We're talking about a UNIQUE constraint
and so what we want to do is prevent the existence of multiple tuples
in a relation that are the same for some defined subset of their
attributes. I would argue that logically, and commonsensically, two
tuples with no attributes are the same, and hence a set of distinct
tuples having zero attributes could have no more than one member, and
so a UNIQUE constraint over zero attributes would say the relation
can't have more than one tuple. So unless someone wants to argue that
two tuples with no attributes are not the same, my interpretation
makes more sense and is clearly the one to follow. -- Darren Duncan
Hmm as a user, I would like at most one row with empty fields covered by
a unique index.
Logical arguments to the contrary, remind me of the joke of the school
boy who told his unlearned father that he had learnt logic and could
prove that his father actually had 3 fish in his basket despite both
seeing only 2 fish. His unlearned father did not try to argue, and
simply said: well your mother can have the first fish, I'll have the
second, and that his learned son could have the third...