From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
---|---|
To: | pdxpug(at)postgresql(dot)org |
Subject: | Re: Adding Missing Data to a Table |
Date: | 2011-03-29 21:06:11 |
Message-ID: | alpine.LNX.2.00.1103291358420.28359@salmo.appl-ecosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pdxpug |
On Tue, 29 Mar 2011, Michael Ewan wrote:
> Are you planning on using 'record' as a PK or simply an index? Will it be an
> FK constraint on some other table.
Michael,
As a PK. There will be other tables linked to it.
> Is there a combination of columns that would be unique so you could use
> them as a composite primary key.
I suppose I could use location + sample_date + parameter but a single
column would be simpler.
> It is generally a bad idea to use a synthetic key as a primary key.
Yeah, that's Joe Celko's view, too. But, there is no natural key in these
data. There's a reference number (called 'record') and a sample number
(one or both probably assigned by the analytical lab).
> ... there is no significance to the record, then don't use it.
It's been assigned by the client company. I doubt they'd throw in a column
for which they had no use.
> The only way you can safely update that column is using a unique primary
> key, and if you create a unique primary key, you don't need the 'record'
> column anymore.
What I want is to use the record key as the unique primary key. This is
NOT a postgresql assigned rowid or OID, this is part of the data. Think
column name 'record' is to be the unique primary key (redundant, I know).
However, to the best of my knowledge I cannot add the table constraint of
primary key to that column until the NULLs are replaced by values. How best
to do that is what I need to learn.
Is this a job for PL/pgSQL or PL/ggPython?
Rich
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2011-03-29 21:07:57 | Re: Adding Missing Data to a Table |
Previous Message | Michael Ewan | 2011-03-29 20:58:12 | Re: Adding Missing Data to a Table |