On Thu, Mar 1, 2018 at 1:07 PM, Rakesh Kumar <rakeshkumar464@aol.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.
