Re: Foreign key constraint with literal?

Justin,
You could create a "clone" of the PART_MASTER table, call it "OBSOLETE_PARTS" ...  (with data) -- e.g. use CRTDUPOBJ, and then use SQL to do a "DELETE ... WHERE OBSOLETE = '1' from PART_MASTER, and DELETE ... WHERE OBSOLETE = '0' from OBSOLETE_PARTS.
Then you would need to add just one trigger to the PARTS_MASTER table as an "instead of" trigger, IIRC, so that when an update happens, to change the status from OBSOLETE = '0' to '1', it will insert that record into the OBSOLETE_PARTS table, and then delete that record from the PARTS_MASTER table.
If you ever need a consolidated view of all the parts, both "active" and "obsolete" you could then just do a JOIN of those two tables.
Might that approach work for you?
Hope that helps.
All the best,
Mark S. Waterbury

On Tuesday, April 27, 2021, 10:01:35 AM EDT, Justin Taylor <jtaylor.0ab@xxxxxxxxx> wrote:

Here's the situation.  I have a parent table, PART_MASTER, with several
child tables (e.g. PROMOTIONS).  The tables have a foreign key relationship
(PART_MASTER.PART_NO = PROMOTIONS.PART_NO).  PART_MASTER also has a column
to indicate obsolete parts.  I need the relationships to be
(PART_MASTER.PART_NO = PROMOTIONS.PART_NO and PART_MASTER.OBSOLETE = '0').

I've tried everything I can think of, but I can't get this to work.  Am I
going to have to create triggers over every table involved?

TIA