Re: Foreign key constraint problem

I'm horsing around with redefining some files with SQL instead of DDS
based
on the redbook authored by Birgitta and others.

I retrieved the SQL for my item master file, prettied it up, and created
it
in a test library. I put some constraints on fields and everything.

I decided to try a foreign key, like this:

ALTER TABLE JEFF/DMITMMST
CONSTRAINT CatalogFK FOREIGN KEY (CATCD1)
REFERENCES DMCATMST (CTCODE)
On Delete Restrict
On Update Restrict ;

CATCD1 is the field name in the item master, and CTCODE is the field name
in
the catalog master. Both defined 1A. The constraint fails with the
following in the job log:

Message ID . . : CPD32B9 Severity . . . : 20
Message type . : Diagnostic
Date sent . . : 05/31/07 Time sent . . : 14:42:12

Message . . . . : Physical file constraint not valid.
Cause . . . . . : An add physical file constraint operation was not
valid
because of reason 1.
1 -- The sort sequence tables are not compatible.

<snip>

Recovery . . . :
1 -- Referential constraints between file DMCATMST in library JEFF and
file DMITMMST in library JEFF cannot be added. The sort sequence tables
must match before a referential constraint can be added.


The SRTSEQ of the SQL defined item master is:

Sort Sequence SRTSEQ
Table Name . . . . . . . : QLA10025S
Library . . . . . . . . : QSYS

The SRTSEQ of the DDS defined catalog master is:

Sort Sequence . . . . . . . : SRTSEQ *HEX

Just for fun I tried a CHGPF FILE(JEFF/DMITMMST) SRTSEQ(*HEX). Does not
work, I get "Parameter not allowed with SRCFILE(*NONE)". Both files are
CCSID 37.

Does this mean I cannot put any foreign key constraints from an SQL file
to
a DDS file?

Thanks.

PS. Interestingly, I assumed I would get level checks if I tried to use
this new file. I don't. I can do a CPYF from my production DDS item
master
to this SQL item master without a hiccup. The application program I tried
against it also works without issue. I didn't expect that.

--
Jeff Crosby
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531

The opinions expressed are my own and not necessarily the opinion of my
company. Unless I say so.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.