From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: Re: unique constraint instead of primary key? what
Date: Thu, 9 Feb 2006 15:10:18 -0500
Message-ID: <20060209151018.58bec92d.gry@ll.mit.edu> (raw)
In-Reply-To: <1139502179.764894.35960@o13g2000cwo.googlegroups.com>
References: <20060208210455.5d6f5bf9.gry@ll.mit.edu>
<1139502179.764894.35960@o13g2000cwo.googlegroups.com>
On 9 Feb 2006 08:22:59 -0800
"BigSmoke" <bigsmoke@gmail.com> threw this fish to the penguins:
> If my tables have one or more UNIQUE constraints/indices, I still add a
> "id SERIAL PRIMARY KEY" field to most of my tables. This makes
> referencing easier and faster. It also improves consistency, which is
> never a bad thing in my opinion.
In this schema overhaul I'm trying to *eliminate* arbitrary "id" columns like
this. They may sometimes improve performance, but certainly obscure the
meaning of the data -- naive [read-only] users accessing through excel are
confused by this sort of thing.
> As far as I know, though, PRIMARY KEY does the same thing as UNIQUE NOT
> NULL in PostgreSQL.
>
> The reason that PRIMARY KEY can't be NULL and _has to be_ UNIQUE is
> that it is the primary means of identifying a given record in a table.
> If you don't have PRIMARY KEY that is UNIQUE and NOT NULL, how are you
> going to identify (or reference) individual records? PostgreSQL won't
> allow you to reference more than one row for what I came to believe are
> very good reasons.
Actually, I don't really see a problem here. E.g. when I want data from
a step that has no parent run and no parent opset, I could say:
select * from steps where run is null and opset is null and step='mystep';
I don't understand what you mean by "PostgreSQL won't allow you to
reference more than one row". If the above query returns 10 rows,
what's wrong with that? Nothing *requires* a PRIMARY KEY at all, anyway.
-- puzzled...
George
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gry@ll.mit.edu
Subject: Re: unique constraint instead of primary key? what
In-Reply-To: <20060209151018.58bec92d.gry@ll.mit.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox