Re: Foreign key

On 7 Jan 1999 15:10:34 +0700, "Guntur Kosasih" <guntur_at_jaknet.com> wrote:

>I have exchange rate table (has column: currency_code, valid_date_from,
>valid_date_to, exchange_rate; Primary key is currency_code and
>valid_date_from) and transaction table (has column: trx_no, trx_date,
>currency_code, amount_in_trx).
>
>The problem is how can I create foreign key to exchange rate table, so I
>can know exchange_rate for every trx_date.
>
>Can anybody help me out here????
>
>Thanks in Advance.....
>
>Guntur Kosasih
>guntur_at_jaknet.com

The problem is that the date in the trade does not refer to a value in the exchange rate table, therefore you cannot define a foreign key relationship. To validate data on entry, you must use a trigger. For retrieval you could use one of the following:

1. Complex join

select	exrate.exchange_rate,
   	trx.trade_date,
	trx.amount_in_trx
from	exrate,
	trx
where 	trx.currency_code = exrate.currency_code
and 	trx.trx_date between exrate.valid_date_from 
	and exrate.valid_date_to

Problems:

	allowing for null values in valid_date_to and valid_date_from.
	performance - access path may be difficult to optimise.
	allowing for missing date ranges

2. Populate a table of exchange rates with a rate for each day using pl/sql. This can provide good performance at the cost of disk space and duplicate data.          Received on Sat Jan 09 1999 - 08:38:16 CST