Re: adding support for zero-attribute unique/etc keys - Mailing list pgsql-hackers

Darren Duncan wrote:
>> The standard defines UNIQUE on the basis of the "UNIQUE predicate":
>> <unique predicate> ::= UNIQUE <table subquery>
>> and states:
>> 1) Let T be the result of the <table subquery>.
>> 2) If there are no two rows in T such that the value of each column
>>    in one row is non-null and is not distinct
>>    from the value of the corresponding column in the other row,
>>    then the result of the <unique predicate> is
>>    *True*; otherwise, the result of the <unique predicate> is *False*.
>>
>> Since an imagined zero-column query would have an empty set of
>> result columns, you could with equal force argue that these columns
>> satisfy the condition or not, because the members of the empty
>> set have all the properties you desire.
>>
>> So I see no compelling argument that such a UNIQUE constraint
>> would force a single-row table.
> 
> I do see that compelling argument, and it has to do with identities.
> 
> The above definition of "UNIQUE predicate" says that the UNIQUE predicate is
> FALSE iff, for every pair of rows in T, the 2 rows of any pair are the same.

I don't understand that sentence.
I would say that it is FALSE iff there exist two rows in T
that satisy:
a) each column in both rows is not-null
b) each column in one of the rows is not distinct from  the corresponding column in the other row

> Further, 2 rows are the same iff, for every corresponding column, the values in
> both rows are the same.  Further, 2 such values are the same iff they are both
> not null and are mutually not distinct.
> 
> 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.
> 
> Hence, a UNIQUE constraint over zero columns signifies a row-comparison
> predicate that unconditionally results in TRUE, and so no two rows at all would
> be allowed in the table with that constraint at once, thus restricting the table
> to at most one row.
> 
> 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.

Yours,
Laurenz Albe