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(at)chromalloy(dot)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