IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

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


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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

    Moderator Emeritus


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

    PostPosted: Sun Mar 25, 2007 4:38 am
    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
    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
    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
    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
    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 View Bookmarks
    All times are GMT + 6 Hours
    Forum Index -> DB2

     


    Similar Topics
    Topic Forum Replies
    No new posts Error to read log with rexx CLIST & REXX 11
    No new posts Error when install DB2 DB2 2
    No new posts CLIST - Virtual storage allocation error CLIST & REXX 5
    No new posts Error while running web tool kit REXX... CLIST & REXX 5
    No new posts Getting Error while trying to establi... DB2 3
    Search our Forums:

    Back to Top