Re: Is PRIMARY KEY the same as UNIQUE NOT NULL?

On Sun, Jan 31, 2016 at 10:17 AM, Bill Moran <wmoran(at)potentialtech(dot)com>
wrote:

> On Sun, 31 Jan 2016 18:02:38 +0100
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com> writes:
> > > Ben Leslie <benno(at)benno(dot)id(dot)au> writes:
> > >> "Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT
> NULL"
> > >>
> > >> I wanted to clarify if that was, technically, true.
> >
> > > Yes, but see below.
> >
> > >> "identifying a set of columns as primary key also provides metadata
> > >> about the design of the schema, as a primary key implies that other
> > >> tables can rely on this set of columns as a unique identifier for
> > >> rows."
> >
> > Yeah. The extra metadata has several other effects. Perhaps it would be
> > better to reword this sentence to make it clear that PRIMARY KEY is
> > equivalent to UNIQUE+NOTNULL in terms of the data constraint that it
> > enforces, without implying that there is no other difference. I'm not
> > sure about a short and clear expression of that though ...
>
> How about:
>
> "PRIMARY KEY is merly a combination of UNIQUE and NOT NULL with regard
> to data consistency behavior."
>
> "identifying a set of columns as primary key also provides metadata about
> the design of the schema, as a primary key implies that other tables can
> rely on this set of columns as a unique identifier for rows. This
> metadata may be used by external programs, but is also utilized interally
> by the server in some cases."
>

​Do we have to be so vague...

A PRIMARY KEY enforces a UNIQUE, NOT NULL constraint and additionally
allows the server to exhibit additional behaviors based upon the additional
knowledge that the chosen constraint unique identifies a specific record.
The behaviors are: <list them here>. External programs and extensions may
also make use of the additional meta-data communicated through the use of
PRIMARY KEY instead of a simple UNIQUE+NOTNULL constraint.

David J.