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