--- Richard Huxton <[EMAIL PROTECTED]> wrote: > 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
Actually a type1_id can have mutiple corresponding transaction_ids (same thing for type2) that's why i created the tables as follows: create table transaction( transaction_id serial P K, amount int,...) create table TABLE_TYPE1( type1_id serial P K, ... ) create table transaction_type1( type1_id int, transaction_id int ) for example we can have the following possible entries in table transaction_type1: type1_id,transaction_id 100,101 100,102 100,103 200,312 200,313 200,314 200,315 Same thing for type 2. I can also add that a transaction id can be of type1 or (exclusive) of type2 and never of two types at the same time. __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
