RE: Active record code referential constraint.

Now that I understand the requirement, trigger looks like the best solution.

That said, if you really want to use constraints (as db theory teaches us we
should), or just want to play around with an alternative solution, something
like this may work:

1) Make OCTIIM's primary key: IPROD,IID /* or a unique constraint if you
already have a primary key */
2) Add IID field to OCTECL table.
3) add a foreign key constraint on OCTECL on fields IPROD,IID with UPDATE
CASCADE DELETE RESTRICT rules

That ought to do it.
When someone "Changes coctiim from 'IM' to 'IZ'", UPDATE will cascade it to
OCTECL table (they'll also become 'IZ'). Since parent table doesn't have
any IM rows in it, foreign key constraint will prevent inserts into the
child table with IM code.
Of course, it would be very prudent for that 'someone' to also add a check
constraint on the parent table so nobody can come along and reinsert the
'IM' value in it for that IPROD (table check constraint).

Hope that makes sense. It's not as straightforward as trigger and involves
some data duplication in the child table, but it would allow you to leverage
referential constraints as a solution.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: Active record code referential constraint.

octiim has
IID='IM'
IPROD='TESTPART'

OCTECL has
LID='CL'
LORD=1
LLINE=1
LPROD='TESTPART'

LPROD matches iprod from parent file OCTIIM.

Now, someone says no more new orders for 'TESTPART'. Changes IID of
octiim from 'IM' to 'IZ'. Existing octecl row can go through completion.
But I want to stop any new rows from appearing in OCTECL for 'TESTPART'.
Any attempt to add a check constraint into octecl on iid results in
column iid not in table octecl.


Rob Berendt