Re: refential integrity to multiple tables ??

On Wednesday 08 October 2003 06:53, Nagib Abi Fadel wrote:
> HI,
>
> let's say i have a tansaction table called TRANSACTION
> (transaction_id,amount,type,type_id)
>
> Let's say a transaction can have multiple types: TYPE1, TYPE2 for example.
>
> EACH type has his own definition and his own table.
>
> Every transaction has a type that could be type1 or type2 that's why if the
> type is TYPE1 i want to make a referential integrity to the TYPE1_TABLE and
> if the type is TYPE2 i want to make a referential integrity to the
> TYPE2_TABLE.
>
> IS IT POSSIBLE TO DO THAT???

You're looking at it the wrong way around, but in any case there are still
problems.

  transaction_core(trans_id, trans_name, trans_type)
  transaction_type1(tt1_core_id, tt1_extra1, tt1_extra2...)
  transaction_type2(tt2_core_id, tt2_extra1, tt2_extra2...)

And have tt1_core reference trans_id (not the other way around). Do the same
for tt2_core and we can guarantee that the two transaction types refer to a
valid trans_id in transaction_core.

Now, what gets trickier is to specify that tt1_core should refer to a row in
transaction_core where trans_type=1.
Ideally, we could have a foreign-key to a view, or specify a constant in the
FK definition. We can't so you have to repeat the type field in
transaction_type1/2 and keep it fixed for every row.

HTH
--
  Richard Huxton
  Archonet Ltd