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


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