I see what's going on Jeff. I am just a messenger so please don't shoot me
:)
In my opinion, you have sufficient set up for the referential key constraint
to be added. For some reason unknown to me, IBM decided that unique
constraint in DDS does not qualify as primary key constraint. To find out
why, you'll have to ask IBM, they may have had a very good reason for it.
To remedy your current conundrum, you'll need to add a primary key
constraint to your DMCATMST file prior to running your ALTER TABLE to add a
referential constraint, i.e.:
ADDPFCST FILE(JEFF/DMCATMST) TYPE(*PRIKEY) KEY(CTCODE)
CST(DMCATMST_PRIMARY_KEY)
I strongly believe that new primary key constraint ought to share the access
path of the unique key you have on the physical file, so from performance
standpoint you shouldn't incur the penalty of maintaining another access
path.
HTH, Elvis
Celebrating 10-Years of SQL Performance Excellence
http://centerfieldtechnology.com/training.asp
-----Original Message-----
Subject: RE: Foreign key constraint problem
Here's the DDS for DMCATMST:
A UNIQUE
A R CTDSCREC TEXT('Catalog Master Record')
A CTRCOD 2 TEXT('Record Code')
A CTACRC 1 TEXT('Active/Deleted')
A CTCODE 1 TEXT('Catalog Code')
A CTDESC 30 TEXT('Catalog Description')
A CTIBUD 9P 2 TEXT('Catalog Inventory Budget')
A CTSALC 1 TEXT('Retail Sales Code')
A CTBALC 1 TEXT('Round Data Cost To 2 Decimal')
A CTGLAC 3P 0 TEXT('Catalog GL Sales Account')
A CTGLSA 1 TEXT('Catalog GL Sales Sub-Acct')
A CTTMOE 1 TEXT('Always on Order Guide Code')
A CTSQOE 3P 0 TEXT('Sequence on Order Quides')
A CTCOMP 2S 0 TEXT('Company')
A CTDOCK 1 TEXT('Receiving Dock')
A CTINSL 1 TEXT('Catalog In Sales Code')
A CTDBUD 4P 1 TEXT('Catalog Days On Hand Budget')
A CTSBUD 9P 2 TEXT('Catalog Sales Budget')
A K CTCODE
And here is the relevant portion of DSPFD:
Access Path Description
Access path maintenance . . . . . . . . . . : MAINT *IMMED
Unique key values required . . . . . . . . : UNIQUE Yes
Access path journaled . . . . . . . . . . . : No
Access path . . . . . . . . . . . . . . . . : Keyed
Constraint Type . . . . . . . . . . . . . . : NONE
Number of key fields . . . . . . . . . . . : 1
Record format . . . . . . . . . . . . . . . : CTDSCREC
Key field . . . . . . . . . . . . . . . . : CTCODE
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : UNSIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Sort Sequence . . . . . . . . . . . . . . . : SRTSEQ *HEX
Language identifier . . . . . . . . . . . . : LANGID ENU
As you can see, CTCODE is a unique key. It must have something to do with
being defined vis DDS.
(Though I could reboot and see if that fixes it, like Windows <g> )
midrange.com
