Let's say you have a one-to-many relationship. The 'one' file may be your
header file with customer information; this is a 'parent' file. It MUST have
a unique key CONSTRAINT, either via a primary key or unique constraint
(UNIQUE keyword in DDS will not suffice, but when followed by ADDPFCST
[*UNQCST or *PRIKEY], it will).
Then you have your 'many' file, let's say it's your orders file; this is a
'child' file. There may be many orders for a particular customer. You want
to establish a relationship between the two stating "every row in the orders
file must have a matching key in the parent file (header)". So now you add
a foreign key constraint on the child table (orders), referencing the
primary key in the parent file (header).
But to put it in (better) English, here is an article from IT Jungle:
http://www.itjungle.com/mpo/mpo021303-story02.html
SQL keyword is REFERENCES and equivalent CL keyword on the ADDPFCST is
*REFCST. I'm sure if you google it, you'll find more examples.
When I add them in SQL, I put them directly in the CREATE TABLE syntax
rather than adding them later via the ALTER TABLE. It's simply a matter of
preference there and I've seen it done both ways.
Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: Referential Constraint
I have been going through the SQL manual most of the morning trying to
figure out how to add a referential constraint on a table that is based
upon values in another table.....
Does anyone have a sample that I could take a look at? The SQL manual
leaves a tad to be desired on that.
midrange.com
