Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

-538, ERROR: FOREIGN KEY DOES NOT CONFORM TO desc of prmkey

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Raj mishra

New User


Joined: 22 Jan 2007
Posts: 2
Location: hyderabad

PostPosted: Sun Mar 25, 2007 12:33 am    Post subject: -538, ERROR: FOREIGN KEY DOES NOT CONFORM TO desc of prmkey
Reply with quote

in my table1 there are 3(three) primary key and in my second i want use one prmary key as foregn key. plz give me right path
table 1
*********************************************
CREATE TABLE PINFO(CUSID NUMERIC(10) NOT NULL,CUSNAM CHAR(20)
NOT NULL,DOB CHAR(10) NOT NULL,OCUP CHAR(20) NOT NULL,
LOC CHAR(20) NOT NULL,CITY CHAR(18) NOT NULL,
STATE CHAR(15) NOT NULL,PIN NUMERIC(6) NOT NULL,
PHONE NUMERIC(11) NOT NULL,MOBILE NUMERIC(10) NOT NULL,
EMAIL CHAR(30) NOT NULL,
PRIMARY KEY(CUSID,PHONE,MOBILE,EMAIL))
IN SSSGENDB.SSSGENTS;
Table=2
*************************************************
CREATE TABLE ACINFO(ACTNO NUMERIC(16) NOT NULL, 00160023
CUSID NUMERIC(10) NOT NULL,ACTYPE CHAR(1) NOT NULL, 00170023
OPNBAL NUMERIC(10) NOT NULL, 00190024
OPNDAT CHAR(11) NOT NULL,PRIMARY KEY(ACTNO), 00200024
FOREIGN KEY(CUSID) REFERENCES PINFO(CUSID)) 00220024
IN SSSGENDB.SSSGENTS; 00230024
-------+---------+---------+---------+---------+---------+---------+---------+
NT408I SQLCODE = -538, ERROR: FOREIGN KEY CUSID DOES NOT CONFORM TO THE
DESCRIPTION OF A PARENT KEY OF TABLE SSS070.PINFO
SQLSTATE = 42830 SQLSTATE RETURN CODE
SQLERRP = DSNXICKK SQL PROCEDURE DETECTING ERROR
SQLERRD = 160 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'000000A0' X'00000000' X'00000000' X'FFFFFFFF'
    Back to top
    View user's profile Send private message

    dick scherrer

    Site Director


    Joined: 23 Nov 2006
    Posts: 19270
    Location: Inside the Matrix

    PostPosted: Sun Mar 25, 2007 4:38 am    Post subject:
    Reply with quote

    Hello,

    I believe you have your definitions "reversed".

    The components of your compound key might refer to the keys of (an)other table(s), but i do not believe your ACINFO table may have a foreign key that is only part of the PINFO key.
    Back to top
    View user's profile Send private message
    vini_srcna

    Active User


    Joined: 26 May 2005
    Posts: 178
    Location: Copenhagen, Denmark

    PostPosted: Mon Mar 26, 2007 10:20 am    Post subject:
    Reply with quote

    in my table1 there are 3(three) primary key

    Major correction: A table can contain one and only one primary key.

    I guess you wanted to say that the PK is built on 3 columns.
    Back to top
    View user's profile Send private message
    Raj mishra

    New User


    Joined: 22 Jan 2007
    Posts: 2
    Location: hyderabad

    PostPosted: Mon Mar 26, 2007 10:42 am    Post subject:
    Reply with quote

    hello,

    in a Table three field contain primary key and i use one filed as a foreign key in another table. is it possible or not?

    hope i will get help
    Back to top
    View user's profile Send private message
    devidas-patil

    New User


    Joined: 08 Jan 2007
    Posts: 54
    Location: pune

    PostPosted: Mon Mar 26, 2007 10:53 am    Post subject:
    Reply with quote

    yes
    i think it is possible to use one of the column of the composite key as foreign key in another table.
    Back to top
    View user's profile Send private message
    vini_srcna

    Active User


    Joined: 26 May 2005
    Posts: 178
    Location: Copenhagen, Denmark

    PostPosted: Mon Mar 26, 2007 2:57 pm    Post subject:
    Reply with quote

    It is not possible. Parent key should be either a unique key or a Primary key.

    In your case, PINFO.CUSID could have duplicate values. It is a part of Pk. You cannot build this relation ship in order to enfore referentail constraints.
    Back to top
    View user's profile Send private message
    View previous topic :: :: View next topic  
    Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
    Page 1 of 1

     

    Search our Forum:

    Similar Topics
    Topic Author Forum Replies Posted
    No new posts DB2 Program abending without giving a... rahulgarg14 DB2 7 Mon May 29, 2017 8:10 pm
    No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
    No new posts IDC2902I Error tzeche ABENDS & Debugging 2 Tue Mar 28, 2017 11:39 am
    No new posts Need help with below DB2 error ashek15 DB2 3 Sat Mar 18, 2017 5:56 am
    No new posts FINDREP Syntax Error seahawk789 DFSORT/ICETOOL 4 Thu Mar 09, 2017 10:11 pm


    Facebook
    Back to Top
     
    Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us