Is PRIMARY KEY the same as UNIQUE NOT NULL?

From: Ben Leslie <benno(at)benno(dot)id(dot)au>
To: pgsql-general(at)postgresql(dot)org
Subject: Is PRIMARY KEY the same as UNIQUE NOT NULL?
Date: 2016-01-31 06:41:42
Message-ID: CABZ0LtDO+Y+7scBS2Q1Li7uyfZfj_5GHLbVUnUTwsbb68GU-ww@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Harald Fuchs 2016-01-31 09:00:47 Re: Is PRIMARY KEY the same as UNIQUE NOT NULL?
Previous Message Adrian Klaver 2016-01-30 20:40:25 Re: Cannot start the PostgreSQL service