Re: [GENERAL] M:M table conditional delete for parents
[EMAIL PROTECTED] wrote: Postgresql 8.1.4 on Redhat 9 I have a table which stores M:M relationships. I can't put foreign keys to the parents of this table because the relationships being stored go to several tables. This was done so that only two fields have to be searched in order for all relationships to be found for an item. For an oem number there might be 50 to 100 relationships and 40 different tables having to do with materials, locations, revisions, specifications, customer, etc. that might be referenced. Have you considered creating real cross-reference tables (aka M:M) between all pairs of tables, and then having a view that UNIONs them together? This way you don't have to re-invent the foreign key to get it all working. -- Kenneth Downs Secure Data Software, Inc. www.secdat.com / www.andromeda-project.org Office: 631-689-7200 Cell: 631-379-0010 ::Think you may have a problem with programming? Ask yourself this ::question: do you worry about how to throw away a garbage can?
Re: [GENERAL] M:M table conditional delete for parents
Kenneth Downs [EMAIL PROTECTED] wrote on 03/06/2007 05:48:05 AM: [EMAIL PROTECTED] wrote: Postgresql 8.1.4 on Redhat 9 I have a table which stores M:M relationships. I can't put foreign keys to the parents of this table because the relationships being stored go to several tables. This was done so that only two fields have to be searched in order for all relationships to be found for an item. For an oem number there might be 50 to 100 relationships and 40 different tables having to do with materials, locations, revisions, specifications, customer, etc. that might be referenced. Have you considered creating real cross-reference tables (aka M:M) between all pairs of tables, and then having a view that UNIONs themtogether? This way you don't have to re-invent the foreign key to get it all working. -- Kenneth Downs Secure Data Software, Inc. www.secdat.com / www.andromeda-project.org Office: 631-689-7200 Cell: 631-379-0010 ::Think you may have a problem with programming? Ask yourself this ::question: do you worry about how to throw away a garbage can? LOL, I actually thought of this late yesterday afternoon. At first I thought this idea would not work because of the number of tables. Then I decided I might be able to categorize the junction tables into 4 or 5 groups, and make a view for each group. Each view would use 15 to 20 tables. This plan is better than working with 50- 100 individual junction tables. As you suggested using the foreign key structure that already exists in Postgresql is an easier way to go. Cheers, *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
Re: [GENERAL] M:M table conditional delete for parents
I think a foreign key restraint is basically a trigger that throws an exception (RAISE statement) when the restraint is violated. Something trigger function like: If table1 if not in table1 raise else if table2 if not in table2 raise end I think that should work, but I've never tried it. [EMAIL PROTECTED] wrote: Postgresql 8.1.4 on Redhat 9 I have a table which stores M:M relationships. I can't put foreign keys to the parents of this table because the relationships being stored go to several tables. This was done so that only two fields have to be searched in order for all relationships to be found for an item. For an oem number there might be 50 to 100 relationships and 40 different tables having to do with materials, locations, revisions, specifications, customer, etc. that might be referenced. Is there some way I can make a mock foreign key restraint on the parents so the parent would search the M:M table for a matching value in key1 if the relate-key is 22, 23, 25 or 100 before it allows the row to be deleted? relate-keyrelate-typekey1table1 key2 table2 22product-material23oem545 material 22product-material23oem546 material 23product-engine23oem 15engine 25product-stage23oem3 stage 100product-revision23oem2270 specifications *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles ---(end of broadcast)--- TIP 6: explain analyze is your friend
