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

On 7 February 2016 at 21:04, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Geoff Winkless <pgsqladmin(at)geoff(dot)dj> writes:
>> On 31 January 2016 at 19:53, David G. Johnston
>> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>> A PRIMARY KEY enforces a UNIQUE, NOT NULL constraint and additionally allows
>
>> I would just remove the whole paragraph. A primary key does what it
>> does, a unique constraint does what it does. I'm not really sure why
>> you need to link them.
>
> I think it is useful to compare them; the only problem is claiming that
> they're equivalent.
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c477e84fe2471cb675234fce75cd6bb4bc2cf481

I maintain that

", which are functionally almost the same thing,"

just muddies the waters for no reason: I would make it

"(There can be any number of constraints that are unique and not-null
but only one primary key.)"

It makes it clear that anyone who wants to achieve the unique-not-null
nature of the PK on two sets of columns can do so, but anyone looking
for any other feature of the PK will not get confused into thinking
that there may be some equivalency that does not exist.

(note I also reworded unique and not-null constraints, since otherwise
there's a potential confusion whether you mean you can have any number
of unique constraints and any number of not-null constraints or
whether you mean any number of "unique and not-null" constraints...)

Geoff