From: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
---|---|
To: | Grupo PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Primary Key |
Date: | 2007-11-18 04:19:01 |
Message-ID: | 473FBD35.40707@cox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 11/17/07 01:21, Gregory Stark wrote:
> "Ron Johnson" <ron(dot)l(dot)johnson(at)cox(dot)net> writes:
>
>> On 11/16/07 12:50, João Paulo Zavanela wrote:
>>> Hello,
>>>
>>> How many fields is recomended to create a primary key?
>>> I'm thinking to create one with 6 fields, is much?
>> The number of recommended fields is the *minimum* number required
>> for uniqueness. 1 or 6 or 24. Doesn't matter.
>
> Unless of course you care about every other table being 24x larger and slower
> due to having all these copies of the 24 fields. And of course unless you care
> about being able to handle the inevitable day when it turns out the 24 fields
> aren't unique and you need to consider adding a 25th column to the table *and
> every table referencing it* as well as changing every line of application code
> to use the new column.
What's got to be done has got to be done.
On one of our systems, the natural PK of an electronic road toll is:
ETC_ACCOUNT_ID INTEGER
FISCAL_PERIOD INTEGER
LANE_TX_ID BIGINT
TX_TYPE_ID CHAR(1)
TX_SUBTYPE_IND CHAR(1)
On another, it's:
ETC_ACCOUNT_ID INTEGER
FISCAL_PERIOD INTEGER
LANE_TX_ID BIGINT
DEVICE_NO CHAR(12) <<<< added column
TX_TYPE_ID CHAR(1)
TX_SUBTYPE_IND CHAR(1)
If the PK was synthetic and generated by the engine, then a (buggy)
app could insert duplicate tolls and the system wouldn't utter a
peep. But the customer sure would when he saw the duplicate entries.
Note the seemingly *synthetic* field LANE_TX_ID.
Records coming in from the lane are inserted into the T_LANE_TX
table which has the PK of LANE_TX_ID. However, that table also has
a "natural" unique index of LANE_ID, TX_DATE, TX_TIME, TX_SEQ_NUMBER
Likewise, T_LANE has the synthetic PK of LANE_ID, but it
back-stopped by a natural unique index PLAZA_ID, EXTERN_LANE_ID.
And... T_PLAZA has the PK of PLAZA_ID and is back-stopped by the
natural unique index AGENCY_ID, EXTERN_PLAZA_ID.
Breaking the chain, T_AGENCY only has the synthetic key AGENCY_ID.
But it only has 27 rows.
- --
Ron Johnson, Jr.
Jefferson LA USA
%SYSTEM-F-FISH, my hovercraft is full of eels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHP701S9HxQb37XmcRAk1cAKCFnNraM2Z1s8M8j8sl8Gotxz1r0QCglEfJ
sUCabkDaZTQVc/kCyHGewhQ=
=b9ii
-----END PGP SIGNATURE-----
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2007-11-18 07:58:23 | Re: how should I do to disable the foreign key in postgres? |
Previous Message | Trevor Talbot | 2007-11-18 02:24:19 | Re: how should I do to disable the foreign key in postgres? |