Re: refential integrity to multiple tables ??

From: Richard Huxton <dev(at)archonet(dot)com>
To: Nagib Abi Fadel <nagib_postgres(at)yahoo(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: refential integrity to multiple tables ??
Date: 2003-10-08 08:56:43
Message-ID: 200310080956.43610.dev@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Együd Csaba 2003-10-08 09:22:12 How to delete unclosed connections?
Previous Message Jean-Christian Imbeault 2003-10-08 08:34:32 install; readline error with 7.3.4