Re: Active record code referential constraint.

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.