Re: Enforce primary key on every table during dev?

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.