I would add
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/dbp/rbaforzahfcoa.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyrefinteg.htm
Basically there's four type of constraints:
- Primary key constraint
- Unique constraint
- Referential constraint
- Check constraint
Primary key constraints ensure that there is only one set of columns that
have identical values in that file, and that it is the primary key. For
example, order number and line number may be your primary key constraint
in your order line file. Item number may be the primary key constraint in
your item number file.
Unique constraints ensure that this column is unique in the database but
it is not necessarily the key. You may put a unique constraint on item
description in your item master file. Not saying that is necessarily a
good idea, just an example.
Referential constraints ensure integrity between two files. For example,
you may put a referential constraint between your item master and your
item class file. This ensures that no item can be assigned an invalid
item class.
If you want an exception, for example an order line contains the item
number and you want referential integrity to your item file UNLESS there
is no item number entered (perhaps a special charge line) then you would
have to use a null value (not blanks!) for your item number.
Check constraints are used to validate the value of an entry. For example
if the only valid values for gender on a patient are Male, Female or
Undetermined (you don't want to hear about these poor children) then you
can put a check constraint on the gender column to ensure it meets one of
these entries.
Constraints do not play well with multimember tables.
If you want the restriction that new order lines can only be created with
"active" item numbers and not items flagged with a soft delete code then
you would probably be better served with a trigger than a constraint. The
trigger would be set on 'insert' only. Not changes. A different change
trigger would ensure that the item number exists at all, active or
inactive.
Here's a cool trick. Do a DSPUSRPRF of all your users to an outfile. Then
do this
CHGPF FILE(yourlib/USERS) MAXMBRS(1)
ADDPFCST FILE(yourlib/USERS) TYPE(*PRIKEY) KEY(UPUPRF)
Now you can read that file with traditional RPG CHAINs and that genre.
No logical file needed.
One reason I prefer DDL over DDS to create tables is that I can put the
constraints right in the CREATE TABLE. With DDS I have to run a "make"
program that adds the constraints after the fact. All it takes is one
clod just compiling the DDS and not using the make program to remove all
your constraints.
Rob Berendt
midrange.com
