RE: Foreign key constraint problem

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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> )