Re: Foreign key constraint with literal?

Hi Justin,

I'm not sure what you mean by "get this to work".

If a part becomes obsolete, I would think that you want to keep it on the PART_MASTER file, but not have it show up in PROMOTIONS, etc. Wouldn't a view handle that?

Well, if I focus on the "I need the relationships to be(PART_MASTER.PART_NO = PROMOTIONS.PART_NO and PART_MASTER.OBSOLETE = '0')" then you could add a field to PROMOTIONS that is defined like the OBSOLETE flag in PART_MASTER, but always has a constant value of '0'.  Then the relationship would be

PART_MASTER.PART_NO = PROMOTIONS.PART_NO and PART_MASTER.OBSOLETE = PROMOTIIONS.OBSOLETE

Of course that's a lot of tables that need to be changed, but perhaps it's a simpler change than adding a trigger program.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx>


/
On 4/27/2021 7:01 AM, Justin Taylor 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