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.
Supporting this gives us more logical completeness, while not allowing lists of
zero attributes is akin to having math without zeroes.
The semantics of having a nullary / zero-attribute unique/primary key on a table
is to restrict the table to having at most one row at a time.
Practically speaking, I find that nullary primary/unique keys are useful in the
numerous situations where one might want to have a table consisting of just one
record (or zero), because it stores data that there is just a single instance
of, such some kinds of config data, rather than a list of such.
Such tables let you store such data in a strongly typed and well self-documented
manner, same as normal tables.
Currently in order to do this, I have to work around the lack of nullary keys by
creating a table with a dummy column having a check constraint saying its value
must equal a constant, and have a unary unique/primary key on that. But this is
an unpleasant kludge and one shouldn't have to do that.
Nullary foreign keys can also be useful, or at least should be supported for
logical completeness. The semantics of having one is to say that one table may
only have rows if another table also has rows.
I was also going to say that it is important to be able to declare a composite
type with zero attributes, which is a way to make a singleton type. However I'm
not sure that we don't already support this so I won't say more on that.
The primary request I have is nullary unique/primary key support, which I see
has a solid use case.
(Note: Logically speaking, if you have a nullary key, it would be the primary
key, and all other unique keys are redundant. Except perhaps that others can
exist in order to support implementation matters of foreign keys that require
their targets to be unique.)
How much work would it be to support this? But also important, does anyone
either agree it should be supported or does anyone want to counter-argue that it
shouldn't be supported?
-- Darren Duncan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
