Re: Foreign key constraint with literal?

[Peter Dow <petercdow@xxxxxxxxx>]

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