Re: Foreign key constraint with literal?

I think that would work. I'm not sure if that would be less effort than
creating trigger programs though.

Thanks

date: Tue, 27 Apr 2021 21:37:26 +0000 (UTC)

from: Mark Waterbury <mark.s.waterbury@xxxxxxxxxxxxx>
subject: 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