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

On 2013.03.25 1:17 AM, Albe Laurenz wrote:
> Darren Duncan wrote:
>>   From my usage and
>> http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html I see that
>> Postgres requires constraints like unique (and primary) keys, and foreign keys,
>> to range over at least 1 attribute/column.
>>
>> I propose that this be generalized so that constraints may instead be nullary,
>> that is, range over zero or more attributes/columns instead.
>
> I don't think that a new way to force single-row tables is
> a good enough use case, and "for completeness' sake" is only
> a good argument if it simplifies things and makes them more
> comprehensible, which I don't think is the case here.
>
> The semantics seem unclear to me:
> 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. 
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?  And can the definition of 
"UNIQUE predicate" reasonably be read the way I stated?  And if the SQL 
definition is ambiguous, could it reasonably be said that the behavior I stated 
is reasonable for Postgres and would match expectations of people using it?

Saying 2 empty rows are the same makes as much sense as saying 2 empty arrays 
are the same, does it not?

> The desired effect can be had today with a unique index:
>
> CREATE TABLE singleton (id integer);
> CREATE UNIQUE INDEX singleton_idx ON singleton((1));

Okay, that is helpful, and less of a kludge than what I was doing, but it is 
still a kludge compared to what I'm proposing, which I see as elegant.

-- Darren Duncan