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