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