Re: adding support for zero-attribute unique/etc keys

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.

[...]

> But also important, does anyone
> either agree it should be supported or does anyone want to counter-argue that it
> shouldn't be supported?

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.

The desired effect can be had today with a unique index:

CREATE TABLE singleton (id integer);
CREATE UNIQUE INDEX singleton_idx ON singleton((1));

Yours,
Laurenz Albe