Re: Active record code referential constraint.

["Charles Wilt" <charles.wilt@xxxxxxxxx>]

I don't think the check constraint could reference the parent table.

Rob, I think a trigger's your best bet.  Particularly if existing
records don't meet the requirement and you want them left alone (you
did say "new rows").

The only way I see this working with constraints is by copying IID
down into the child...

CREATE TABLE ctsc_w/ROBP
(IID CHAR (2 ) NOT NULL WITH DEFAULT, IPROD
CHAR (15 ) NOT NULL WITH DEFAULT, PRIMARY KEY (iid, IPROD))

CREATE TABLE CTSC_W/ROBC (IID CHAR ( 2) NOT NULL WITH DEFAULT,
IPROD CHAR ( 15) NOT NULL WITH DEFAULT, PRIMARY KEY (IID, IPROD),
FOREIGN KEY (IID, IPROD) REFERENCES CTSC_W/ROBP (IID, IPROD) ON
DELETE RESTRICT ON UPDATE RESTRICT, CHECK (IID = 'IZ' ))

HTH,
Charles

On Mon, Oct 13, 2008 at 3:51 PM, Elvis Budimlic
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> Another way would be to add a check constraint on the child table.
>
> Hth, Elvis
>
> Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
> OS/400
> www.centerfieldtechnology.com
>
>
> -----Original Message-----
> Subject: Active record code referential constraint.
>
> Suppose you have the following table:
> Field        Start     End  Attrib  Text
> IID              1       2  A2      Record ID; IM/IZ
> IPROD            3      17  A15     Item Number
> ...
> Is there a way to build a constraint so that no new rows can be added to a
> child table of this table if the parent row is an 'IZ'?  Only if it is an
> 'IM'?
>
> Let's say you have an item master with those columns.  I want it so that
> if IID <>'IM' that you cannot use that item on a new row in the order line
> file.
>
> Am I stuck with a trigger?
>
> Rob Berendt
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.