Обсуждение: Is PRIMARY KEY the same as UNIQUE NOT NULL?

Apologies in advance if this is not the best list for this. Appreciate
a redirection if there is something more appropriate.

In the document is mentions that:

"Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL"

I wanted to clarify if that was, technically, true. I had a table
where I had used "UNIQUE NOT NULL" rather than primary key (not
recommended by the docs, probably not best practise, but I thought, at
the time, if they are equivalent, it should work).

I then had need to use the  "Group by can guess some missing columns"
feature described here:

https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.1#SQL_and_PL.2FPgSQL_features

Unfortunately this feature does not seem to work when using a column
that is merely "UNIQUE NOT NULL", however it does seem to work when I
change the columns to "PRIMARY KEY".

My questions are:

1. Is this intended behaviour.
2. Does this mean, technically, that PRIMARY KEY is not merely a
combination of UNIQUE and NOT NULL?

The documentation does clarify with:

"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."

I had assumed that the "metadata" was schema information that may be
used by external tools or the users of the database, however it seems
that this meta-data is also used at some point in the query engine, so
my assumption that "meta-data" was only for external use seems
incorrect. Are there any other cases that take advantage of PRIMARY
KEY? (Maybe NATURAL joins?).

Thanks,

Ben

Ben Leslie <benno@benno.id.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."

This means that e.g. you can use

  ALTER TABLE othertbl FOREIGN KEY (refid) REFERENCES mytbl

without specifying the column(s) of mytbl.  This implies that there can
be only one primary key (possibly covering more than one column), and
that's the second difference to UNIQUE NOT NULL.

Harald Fuchs <hari.fuchs@gmail.com> writes:
> Ben Leslie <benno@benno.id.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 ...

            regards, tom lane

On Sun, 31 Jan 2016 18:02:38 +0100
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Harald Fuchs <hari.fuchs@gmail.com> writes:
> > Ben Leslie <benno@benno.id.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."

--
Bill Moran

On 01/31/2016 09:02 AM, Tom Lane wrote:
> Harald Fuchs <hari.fuchs@gmail.com> writes:
>> Ben Leslie <benno@benno.id.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 ...

The practical implementation of a PRIMARY KEY is the equivalent to
UNIQUE+NOTNULL. However, ...

>
>             regards, tom lane
>
>


--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.

On 31 January 2016 at 19:53, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> A PRIMARY KEY enforces a UNIQUE, NOT NULL constraint and additionally allows
[snip]

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 would just start with

"A primary key indicates that a column or group of columns can be used
as a unique identifier for rows in the table."

before the examples, then remove the "Technically" and the whole
parenthesised comment about unique keys and nulls, so the next line
after the examples becomes

"Primary keys are useful both for documentation purposes and for
client applications. For example, a GUI application that allows
modifying row values probably needs to know the primary key of a table
to be able to identify rows uniquely."

I just think it's unnecessarily confusing to start suggesting that
there's some equivalency when you then need to clarify that actually
they're not really equivalent.

Geoff

Geoff Winkless <pgsqladmin@geoff.dj> writes:
> On 31 January 2016 at 19:53, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> A PRIMARY KEY enforces a UNIQUE, NOT NULL constraint and additionally allows
> [snip]

> 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.

I've applied some doc updates based on this discussion.

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c477e84fe2471cb675234fce75cd6bb4bc2cf481

            regards, tom lane

On 8 February 2016 at 08:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Geoff Winkless <pgsqladmin@geoff.dj> writes:
>> On 31 January 2016 at 19:53, David G. Johnston
>> <david.g.johnston@gmail.com> wrote:
>>> A PRIMARY KEY enforces a UNIQUE, NOT NULL constraint and additionally allows
>> [snip]
>
>> 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.
>
> I've applied some doc updates based on this discussion.
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c477e84fe2471cb675234fce75cd6bb4bc2cf481
>
>                         regards, tom lane

Thanks Tom,

I think with the documentation expressed this way I don't think I
would have made the error I did originally; from my point of view it
is a welcome improvement.

Cheers,

Ben

On 7 February 2016 at 21:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Geoff Winkless <pgsqladmin@geoff.dj> writes:
>> On 31 January 2016 at 19:53, David G. Johnston
>> <david.g.johnston@gmail.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