I haven't been following this thread for a little while and it looks like I missed something. My apologies if this reply is off the mark.
If the issue is the use of alter table to add constraints at table creation they aren't necessary. I place the constraints directly in my create statement.
Create table #785/TransEdit (
Bank Numeric(3,0) not null,
Trans_Code for TRANCODE Numeric(2,0) not null,
Process_Mode for PROCMODE Char(1) not null
Constraint TRANSEDIT_Process_Mode
Check(Process_Mode in ('B', 'I')),
Loan_Type for LOANTYPE Numeric(3,0) not null,
Interest_Type for INTTYPE Char(1) not null,
Interest_Comp_Code for INTCMPCD Char(1) not null,
Description for DESCR Char(50) not null with default,
Edit_Sequence for EDITSEQ Integer not null with default,
Procedure_Name for PROCNAME Char(128) not null,
Pass_Value for PASSVALUE Char(1) not null
Constraint TRANSEDIT_Pass_Value
Check(Pass_Value in ('1', '0')),
Status Char(1) not null
Constraint TRANSEDIT_Status
Check(Status in ('A', 'I', 'P')),
Add_User for ADDUSER Char(18) not null with default USER,
Add_Timestamp for ADDTMSTP Timestamp not null
with default CURRENT_TIMESTAMP,
Upd_User for UPDUSER Char(18) not null with default USER,
Upd_Timestamp for UPDTMSTP Timestamp not null
with default CURRENT_TIMESTAMP,
-- Create primary key
Constraint TRANSEDIT_Key Primary Key (Bank, Trans_Code, Process_Mode,
Loan_Type, Interest_Type,
Interest_Comp_Code),
-- Referential constraint on loan type
Constraint Valid_TRANSEDIT_Loan_Type
Foreign key (Loan_Type) references LOANTYPE (Loan_Type)
On delete CASCADE
On update RESTRICT,
-- Referential constraint on interest type
Constraint Valid_TRANSEDIT_Interest_Type
Foreign key (Interest_Type) references INTTYPE (Interest_Type)
On delete CASCADE
On update RESTRICT,
-- Referential constraint on interest computation code
Constraint Valid_TRANSEDIT_Interest_Comp_Code
Foreign key (Interest_Comp_Code) references INTCOMPCD (Interest_Comp_Code)
On delete CASCADE
On update RESTRICT);
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Tuesday, May 12, 2009 3:36 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Confused about DDL and DDS
rob@xxxxxxxxx wrote:
Not one of the samples posted here included any constraints:
referential, check or otherwise. All stuff that was impossible
with DDS. And can only be tacked on after the fact with
ADDPFCST.
The accusation that the constraint information can "only be
tacked on after the fact" is disingenuous. That the DDL can include
constraint definitions in the CREATE statement is nice; great even.
However in SQL DDL, constraints are often seen to be added "after
the fact" using the ALTER TABLE even if only for documentation
purposes. For referential constraints it may even be an effective
requirement to do a separate ALTER due to a parent not-yet-created
scenario. Irrespective of when or by what interface, by SQL or the
CL command ADDPFCST, the constraint is still a constraint *and* the
constraints are enforced all the same, irrespective of whether the
data is being manipulated by DML or RLA.
Also FWiW, Lloyd [as the second to reply to the thread] did
actually post the DDL for a CREATE TABLE with a PRIMARY KEY, and
later the script has an ALTER TABLE that added a CHECK CONSTRAINT.
Alan posted one with a slew of ALTERs done post-create to add CHECKs
to several of the columns.
Regards, Chuck
Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.
midrange.com
