RE: Imbedded SQL vs Sequence object

From: rob@xxxxxxxxx

CREATE TABLE TESTLIB/ORDLINE (
ORDERNBR INT NOT NULL,
ORDERLINE INT NOT NULL,
ACTIVE CHAR (1 ) NOT NULL,
ITEMNUMBER CHAR (15) NOT NULL,
ORDERQUANTITY DEC (15, 5),
SOMENBR INT NOT NULL,
CONSTRAINT ORDLINE PRIMARY KEY (ORDERNBR, ORDERLINE),
CONSTRAINT OrdlineOrdhead FOREIGN KEY (ORDERNBR)
REFERENCES TESTLIB/ORDHEAD (ORDERNBR),
CONSTRAINT OrdlineItems FOREIGN KEY (ITEMNUMBER)
REFERENCES TESTLIB/ITEMS (ITEMNUMBER),
CONSTRAINT OrdlineActive CHECK (ACTIVE in('0', '1') ),
CONSTRAINT OrdlinePositive CHECK (ORDERLINE > 0)
)

This code bothers me. Let me explain why, and maybe people can tell me I'm
all messed up. Or maybe it's just a matter of preference.

Anyway, I can live with constraints that are database-related. Foreign
keys, that sort of stuff. But I have a problem with basic editing in the
table definition. Here's why:

Not all edits can be defined as constraints. There are other logical edits
that must be defined in the maintenance program. So when writing the
maintenance program, how do you know which edits are already in place and
which aren't? You need to go to the table definition. Conversely, you
can't just look at the constraints in the table definition to see the
editing on the file, you have to look at the maintenance program.

The upside of course is that for those edits defined as constraints I don't
have to write the rules in RPG or some other language, and they're always
there. But if I'm writing the editing rules anyway, why not keep them all
together in one piece of code? The argument might be that there are many
programs that write records to the file, but to me that's more of an
argument for a better architecture with only a single program that writes
records to the file.

Or is there perhaps a logical cutoff point to determine which edits should
go in the table definition and which should go in code?

(Rob, please understand that I'm not picking on you in any way. This
question is motivated purely by my wanting to understand.)

Joe