Using Foreign Key To Ref A Unique Index

[ken_knee]

I have a PARENT/CHILD I'm attempting to attach another CHILD to. When I try to add a FOREIGN KEY to the 3rd table referring to CHILD1 (AGTERNS), I get error SQL0573N A column list specified in the references clause of constraint
"RCAGTRNZ" does not identify a unique constraint of the parent table
"VCSTSTNT.AGTER NS".

Since CHILD1 has multiple constraints, a unique index and a foreign key, is there a gotcha that I'm not aware of? Thanks a lot.

My field names and lengths all seem to match on the DDL and I've even tried dropping and re-creating the child.

Here is the first child definition:

;
CREATE TABLE VCSTSTNT.AGTERN S
(
COMPANY_CODE CHAR(3) NOT NULL WITH DEFAULT,
AGENT_ID CHAR(10) NOT NULL WITH DEFAULT,
SEGMENT_ID CHAR(2) NOT NULL WITH DEFAULT,
PERIOD_END_DTE DEC(8,0) NOT NULL WITH DEFAULT,
SEQUENCE_NUMBER DEC(2,0) NOT NULL WITH DEFAULT,
BUSINESS_CODE DEC(2,0) NOT NULL WITH DEFAULT,
LEVEL_1_FIRST_Y EAR DEC(11,2) NOT NULL WITH DEFAULT,
OWRITE_FIRST_YE AR DEC(11,2) NOT NULL WITH DEFAULT,
LEVEL_1_RENEWAL S DEC(11,2) NOT NULL WITH DEFAULT,
OVERWRITE_RENEW ALS DEC(11,2) NOT NULL WITH DEFAULT,
TOTAL_ADVANCES DEC(11,2) NOT NULL WITH DEFAULT,
ADV_OFSET_BY_EA RN DEC(11,2) NOT NULL WITH DEFAULT,
ADV_OFF_BY_RECO UP DEC(11,2) NOT NULL WITH DEFAULT,
ADVANCE_BALANCE DEC(11,2) NOT NULL WITH DEFAULT,
LEVEL_1_COMM DEC(11,2) NOT NULL WITH DEFAULT,
OVERWRITE_COMM DEC(11,2) NOT NULL WITH DEFAULT,
RETAIN_COM_BAL DEC(11,2) NOT NULL WITH DEFAULT,
LEVEL_1_COI DEC(11,2) NOT NULL WITH DEFAULT,
OVERWRITE_COI DEC(11,2) NOT NULL WITH DEFAULT,
LEVEL_1_SRV_FEE DEC(11,2) NOT NULL WITH DEFAULT,
OVERWRITE_SRV_F EE DEC(11,2) NOT NULL WITH DEFAULT,
LEVEL_1_ALLOW DEC(11,2) NOT NULL WITH DEFAULT,
OVERWRITE_ALLOW DEC(11,2) NOT NULL WITH DEFAULT,
FOREIGN KEY RCAGTRNS
( COMPANY_CODE,
AGENT_ID,
SEGMENT_ID,
PERIOD_END_DTE )
REFERENCES VCSTSTNT.AGTERN
ON DELETE CASCADE
) IN TAGTERNS
;
CREATE UNIQUE INDEX VCSTSTNT.IAGTRN S1
ON VCSTSTNT.AGTERN S
(
COMPANY_CODE ASC,
AGENT_ID ASC,
SEGMENT_ID ASC,
PERIOD_END_DTE ASC,
SEQUENCE_NUMBER ASC
)
;

Here is the second child definition (AGTERNZ):

CREATE TABLE VCSTSTNT.AGTERN Z
(
COMPANY_CODE CHAR(3) NOT NULL WITH DEFA
AGENT_ID CHAR(10) NOT NULL WITH DEFA
SEGMENT_ID CHAR(2) NOT NULL WITH DEFA
PERIOD_END_DTE DEC(8,0) NOT NULL WITH DEFA
SEQUENCE_NUMBER DEC(2,0) NOT NULL WITH DEFA
BUSINESS_CODE DEC(2,0) NOT NULL WITH DEFA
BONUS_PLAN_ID CHAR(8) NOT NULL WITH DEFA
BONUS_ALLOWANCE S DEC(11,2) NOT NULL WITH DEFA
BONUS_ALLOWANCE _ADVANCES DEC(11,2) NOT NULL WITH DEFA
BONUS_ADV_OFFSE T_BY_EARNINGS DEC(11,2) NOT NULL WITH DEFA
BONUS_ADV_OFFSE T_BY_RECOUPMENT DEC(11,2) NOT NULL WITH DEFA
)
IN TAGTERNZ
;
CREATE UNIQUE INDEX VCSTSTNT.IAGTRN Z1
ON VCSTSTNT.AGTERN Z
( COMPANY_CODE ASC,
AGENT_ID ASC,
SEGMENT_ID ASC,
PERIOD_END_DTE ASC,
SEQUENCE_NUMBER ASC,
BUSINESS_CODE ASC,
BONUS_PLAN_ID ASC )
;
ALTER TABLE VCSTSTNT.AGTERN Z
ADD CONSTRAINT RCAGTRNZ
FOREIGN KEY
( COMPANY_CODE,
AGENT_ID,
SEGMENT_ID,
PERIOD_END_DTE,
SEQUENCE_NUMBER )
REFERENCES VCSTSTNT.AGTERN S
( COMPANY_CODE,
AGENT_ID,
SEGMENT_ID,
PERIOD_END_DTE,
SEQUENCE_NUMBER )
ON DELETE CASCADE
;

Ken H.

[ken_knee]

problem resolved