Re: Lookup Table and Constraints question

It is not possible to create a referential integrity constraint, for a LOOKUP_TYPE = 'x', but you could create a foreign key between the lookup table and the detail table to ensure that the lookup value cannot be accidentally deleted, and then use a trigger on the table, for insert and update purpose to ensure that the value is for type = 'x'.

If you do this, and the application allows for the online delete of the Lookup Table, then be careful because if there is an Index missing on ANY of the detail tables, there could be a big locking issue. When a Master is deleted, and there is no index on the Detail, the detail table is locked preventing updates to the table.

Hope this helps,
Andrew

PMG wrote:

> I have a general purpose lookup table with the following columns:
>
> LOOKUP_TYPE, LOOKUP_VALUE.
>
> Can you create a constraint for a table, so that a particular column in
> that table is restricted to LOOKUP_VALUES for LOOKUP_TYPE = 'x'
>
> TIA
>
> Pete
Received on Fri Apr 09 1999 - 04:23:03 CDT