Re: unique constraint instead of primary key? what

On 9 Feb 2006 08:22:59 -0800
"BigSmoke" <bigsmoke(at)gmail(dot)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)