M:M table conditional delete for parents
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-key relate-type key1 table1 key2 table2
22 product-material 23 oem 545 material
22 product-material 23 oem 546 material
23 product-engine 23 oem 15 engine
25 product-stage 23 oem 3 stage
100 product-revision 23 oem 2270
specifications
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
*** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles
Import Notes
Reply to msg id not found: 20070303185758.41AE4608239@mx1.hub.org
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.
MargaretGillon@chromalloy.com wrote:
Show quoted text
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-key relate-type key1 table1
key2 table2
22 product-material 23 oem 545
material
22 product-material 23 oem 546
material
23 product-engine 23 oem
15 engine
25 product-stage 23 oem 3
stage
100 product-revision 23 oem 2270
specifications*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
*** *** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles
MargaretGillon@chromalloy.com 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?
Kenneth Downs <ken@secdat.com> wrote on 03/06/2007 05:48:05 AM:
MargaretGillon@chromalloy.com 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