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