Thread: 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

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:
>
> 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