From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | Rakesh Kumar <rakeshkumar464(at)aol(dot)com> |
Cc: | Alban Hertroys <haramrae(at)gmail(dot)com>, Melvin Davidson <melvin6925(at)gmail(dot)com>, Tim Cross <theophilusx(at)gmail(dot)com>, Jeremy Finzel <finzelj(at)gmail(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Enforce primary key on every table during dev? |
Date: | 2018-03-01 18:35:17 |
Message-ID: | CA+bJJbxbzmUuYqn_4f5805inEmz2jwABdUYM2OmcEuPM=J4byg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Mar 1, 2018 at 1:07 PM, Rakesh Kumar <rakeshkumar464(at)aol(dot)com> wrote:
...
> I routinely add surrogate keys like serial col to a table already having a nice candidate keys
> to make it easy to join tables. SQL starts looking ungainly when you have a 3 col primary
> key and need to join it with child tables.
It does, but many times useful, let me explain:
table currencies ( code text, description text), primary key code (
i.e. "USD", "US Dollars" )
table sellers ( currency text, id number, .....), primary key
(currency, id), foreign key currency references currencies
table buyers ( currency text, id number, .....), primary key
(currency, id) foreign key currency references currencies
table transactions ( currency text, seller_id number, buyer_id number,
trans_id number ....)
primery key trans_id,
foreign key currency references currencies,
foreign key (currency, seller_id ) references sellers,
foreign key (currency, buyer_id ) references buyers
This is a bit unwieldy, but it expreses my example constraint, buyers
can only buy from a seller with the same currency, there is no way to
insert a cross-currency transaction.
Of course, 3 femtoseconds after deployment the PHB will decide you can
do cross-currency sales.
Francisco Olarte.
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2018-03-01 18:40:43 | Re: Enforce primary key on every table during dev? |
Previous Message | Daevor The Devoted | 2018-03-01 18:32:56 | Re: Enforce primary key on every table during dev? |