SQLCODE -538

Buz W

SQLCODE -538
Help! I'm getting the following error when I run my DDL:
   ALTER TABLE NSYNC02.MIG_ARCH_TEXT                                           
   ADD FOREIGN KEY                                                             
       (                                                                       
             I_MIG_ARCHDR                                                      
   )                                                                           
       REFERENCES NSYNC02.MIG_ARCH_HDR                                         
       (                                                                       
             I_MIG_ARCHDR                                                      
   ) ON DELETE CASCADE;                                                        
SQLERROR ON   ALTER     COMMAND, EXECUTE   FUNCTION                            
 RESULT OF SQL STATEMENT:                                                      
 DSNT408I SQLCODE = -538, ERROR:  FOREIGN KEY I$MIG$AR DOES NOT CONFORM TO THE DESCRIPTION OF A PARENT KEY OF TABLE  
          NSYNC02.MIG_ARCH_HDR                                                 
 DSNT418I SQLSTATE   = 42830 SQLSTATE RETURN CODE                              
 DSNT415I SQLERRP    = DSNXICKK SQL PROCEDURE DETECTING ERROR                  
 DSNT416I SQLERRD    = 162  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION          
 DSNT416I SQLERRD    = X'000000A2'  X'00000000'  X'00000000'  X'FFFFFFFF'  X'00000000'  X'00000000' SQL DIAGNOSTIC   
          INFORMATION                                                          

I've checked about 20 times, but the two I_MIG_ARCHDRs are identical.

According to the Messages manual, there are 3 things that might cause this:
1) The referenced parent key has not been defined as a primary key or a unique key.
     It has been defined as a unique key.
2) The keys do not have the same number of columns.
     It's a single column key.
3) The decription of the keys are not identical. The requirement for identical descriptions includes data type, length attribute, and field procedure.
     In the parent table it's INT, AS IDENTITY, NOT NULL, in the child table it's INT, NOT NULL.

Does anyone have any idea what to look at next? This is on a new database, but we've used Foreign Keys before in other databases.

I'm stumped.

Thanks in advance for any suggestions.

Buz Williams
Dallas, TX
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jeff Frazier

Re: SQLCODE -538
(in response to Buz W)
Buzz,
check out PQ96503 an open APAR. might be your problem:






CREATE TABLE TAXD.TTRCS045_TEST
(
COL1 CHAR(2) NOT NULL,
FOREIGN KEY (COL1)
REFERENCES
TAXD.TTRCS912_TENDER(T912_CODE)
ON DELETE CASCADE
)
IN DTRCS.STRCS912 AUDIT NONE DATA CAPTURE NONE
resulted in:
DSNT408I SQLCODE = -538, ERROR: FOREIGN KEY COL1 DOES NOT
CONFORM TO THE
DESCRIPTION OF A PARENT KEY OF TABLE
TAXD.TTRCS912_TENDER
DSNT418I SQLSTATE = 42830 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICKK SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 162 0 0 -1 0 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'000000A2' X'00000000' X'00000000'


LOCAL FIX:
DROP and reCREATE constraint in the newer release.



















BuzW <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/13/2004 01:12 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: SQLCODE -538


Help! I'm getting the following error when I run my DDL:
ALTER TABLE
NSYNC02.MIG_ARCH_TEXT
ADD FOREIGN
KEY

(

I_MIG_ARCHDR

)

REFERENCES
NSYNC02.MIG_ARCH_HDR

(

I_MIG_ARCHDR
) ON DELETE
CASCADE;
SQLERROR ON ALTER COMMAND, EXECUTE
FUNCTION
RESULT OF SQL
STATEMENT:
DSNT408I SQLCODE = -538, ERROR: FOREIGN KEY I$MIG$AR DOES NOT CONFORM TO
THE DESCRIPTION OF A PARENT KEY OF TABLE

NSYNC02.MIG_ARCH_HDR
DSNT418I SQLSTATE = 42830 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNXICKK SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 162 0 0 -1 0 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'000000A2' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC

INFORMATION

I've checked about 20 times, but the two I_MIG_ARCHDRs are identical.

According to the Messages manual, there are 3 things that might cause
this:
1) The referenced parent key has not been defined as a primary key or a
unique key.
It has been defined as a unique key.
2) The keys do not have the same number of columns.
It's a single column key.
3) The decription of the keys are not identical. The requirement for
identical descriptions includes data type, length attribute, and field
procedure.
In the parent table it's INT, AS IDENTITY, NOT NULL, in the child
table it's INT, NOT NULL.

Does anyone have any idea what to look at next? This is on a new database,
but we've used Foreign Keys before in other databases.

I'm stumped.

Thanks in advance for any suggestions.

Buz Williams
Dallas, TX



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dale Hardy

Re: SQLCODE -538
(in response to Jeff Frazier)
When you say it has been defined as a unique key, did you mean it had a
unique constraint defined and not just a unique index?

_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of BuzW
Sent: Monday, December 13, 2004 1:12 PM
To: [login to unmask email]
Subject: SQLCODE -538


Help! I'm getting the following error when I run my DDL:
ALTER TABLE NSYNC02.MIG_ARCH_TEXT

ADD FOREIGN KEY

(

I_MIG_ARCHDR

)

REFERENCES NSYNC02.MIG_ARCH_HDR

(

I_MIG_ARCHDR

) ON DELETE CASCADE;

SQLERROR ON ALTER COMMAND, EXECUTE FUNCTION

RESULT OF SQL STATEMENT:

DSNT408I SQLCODE = -538, ERROR: FOREIGN KEY I$MIG$AR DOES NOT CONFORM
TO THE DESCRIPTION OF A PARENT KEY OF TABLE
NSYNC02.MIG_ARCH_HDR

DSNT418I SQLSTATE = 42830 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNXICKK SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 162 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'000000A2' X'00000000' X'00000000'
X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION


I've checked about 20 times, but the two I_MIG_ARCHDRs are identical.

According to the Messages manual, there are 3 things that might cause
this:
1) The referenced parent key has not been defined as a primary key or a
unique key.
It has been defined as a unique key.
2) The keys do not have the same number of columns.
It's a single column key.
3) The decription of the keys are not identical. The requirement for
identical descriptions includes data type, length attribute, and field
procedure.
In the parent table it's INT, AS IDENTITY, NOT NULL, in the child
table it's INT, NOT NULL.

Does anyone have any idea what to look at next? This is on a new
database, but we've used Foreign Keys before in other databases.

I'm stumped.

Thanks in advance for any suggestions.

Buz Williams
Dallas, TX
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: SQLCODE -538
(in response to Dale Hardy)
You need to define the column as the PRIMARY key of the parent table.

Carol Sutfin
Corporate DBA
AmSouth Bank
(205)261-5214
[login to unmask email]




"Hardy, Dale"
<[login to unmask email]> To: [login to unmask email]
Sent by: "DB2 cc:
Data Base Subject: Re: SQLCODE -538
Discussion List"
<[login to unmask email]
.ORG>


12/13/2004 12:59
PM
Please respond
to "DB2 Database
Discussion list
at IDUG"





When you say it has been defined as a unique key, did you mean it had a
unique constraint defined and not just a unique index?

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of BuzW
Sent: Monday, December 13, 2004 1:12 PM
To: [login to unmask email]
Subject: SQLCODE -538

Help! I'm getting the following error when I run my DDL:
ALTER TABLE NSYNC02.MIG_ARCH_TEXT

ADD FOREIGN KEY

(

I_MIG_ARCHDR

)

REFERENCES NSYNC02.MIG_ARCH_HDR

(

I_MIG_ARCHDR

) ON DELETE CASCADE;

SQLERROR ON ALTER COMMAND, EXECUTE FUNCTION

RESULT OF SQL STATEMENT:

DSNT408I SQLCODE = -538, ERROR: FOREIGN KEY I$MIG$AR DOES NOT CONFORM TO
THE DESCRIPTION OF A PARENT KEY OF TABLE
NSYNC02.MIG_ARCH_HDR

DSNT418I SQLSTATE = 42830 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNXICKK SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 162 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'000000A2' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION


I've checked about 20 times, but the two I_MIG_ARCHDRs are identical.

According to the Messages manual, there are 3 things that might cause this:
1) The referenced parent key has not been defined as a primary key or a
unique key.
It has been defined as a unique key.
2) The keys do not have the same number of columns.
It's a single column key.
3) The decription of the keys are not identical. The requirement for
identical descriptions includes data type, length attribute, and field
procedure.
In the parent table it's INT, AS IDENTITY, NOT NULL, in the child
table it's INT, NOT NULL.

Does anyone have any idea what to look at next? This is on a new database,
but we've used Foreign Keys before in other databases.

I'm stumped.

Thanks in advance for any suggestions.

Buz Williams
Dallas, TX
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm