Joined: 10 Oct 2006 Posts: 16 Topics: 6 Location: Bangalore
Posted: Thu Dec 07, 2006 12:56 am Post subject: Problem with Foreign Key Contraint
I was preparing for DB2 700 exam and surprised why I am getting foreign key constraint SQLCODE = -530. Can anyone please let me know what I am doing wrong because I feel record must be inserted but ending up in error? I am attaching the spufi output for your reference.
Code:
CREATE TABLE RUSH_EMPLNO
(EMPNO CHAR(5) NOT NULL,
LNAME CHAR(10),
FNAME CHAR(10),
DEPT CHAR(15),
GOVTID CHAR(5),
SALRY INTEGER,
PRIMARY KEY(EMPNO));
---------+---------+---------+---------+---------+---------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------
CREATE UNIQUE INDEX XEMP ON RUSH_EMPLNO (EMPNO);
---------+---------+---------+---------+---------+---------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------
CREATE INDEX XGOVTID ON RUSH_EMPLNO (GOVTID);
---------+---------+---------+---------+---------+---------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------
INSERT INTO RUSH_EMPLNO
VALUES ('00001','LAST01','FIRST01','DEPT01','G0001',0100);
---------+---------+---------+---------+---------+---------
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------
INSERT INTO RUSH_EMPLNO
VALUES ('00002','LAST02','FIRST02','DEPT02','G0002',0200);
---------+---------+---------+---------+---------+---------
DSNE615I NUMBER OF ROWS AFFECTED IS 1 DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+-------
INSERT INTO RUSH_EMPLNO
VALUES('00003','LAST03','FIRST03','DEPT03','G0003',0300);
---------+---------+---------+---------+---------+---------+-------
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+-------
SELECT *
FROM RUSH_EMPLNO;
---------+---------+---------+---------+---------+---------+-------
EMPNO LNAME FNAME DEPT GOVTID SALRY
---------+---------+---------+---------+---------+---------+-------
00001 LAST01 FIRST01 DEPT01 G0001 10000002 LAST02 FIRST02 DEPT02 G0002 200
00003 LAST03 FIRST03 DEPT03 G0003 300
DSNE610I NUMBER OF ROWS DISPLAYED IS 3
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-------
CREATE TABLE RUSH_BEN_CLAIM
(CLAIMNO CHAR(5) NOT NULL,
GOVTID CHAR(5),
SEVICE_DEP CHAR(15),
CLAIM_CODE CHAR(5),
PRIMARY KEY(CLAIMNO),
FOREIGN KEY FK1(GOVTID) REFERENCES RUSH_EMPLNO
ON DELETE RESTRICT);
---------+---------+---------+---------+---------+---------+-------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 ---------+---------+---------+---------+---------+---------+---------+
CREATE UNIQUE INDEX XBEN ON RUSH_BEN_CLAIM (CLAIMNO);
---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+
CREATE INDEX XGOVTID1 ON RUSH_BEN_CLAIM (GOVTID);
---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+
INSERT INTO RUSH_BEN_CLAIM
VALUES ('CL001','G0001','DEPT01','11');
---------+---------+---------+---------+---------+---------+---------+
DSNT408I [b]SQLCODE = -530, ERROR[/b]: THE INSERT OR UPDATE VALUE OF FOREIGN KEY FK1 IS INVALID
DSNT418I SQLSTATE = 23503 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRUID SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -110 13172774 0 0 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF92' X'00C90026' X'00000000' X'00000000'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-----
DSNE614I AUTOCOMMIT IS NO; NO CHANGES COMMITTED
Joined: 10 Oct 2003 Posts: 315 Topics: 49 Location: Germany
Posted: Thu Dec 07, 2006 2:54 am Post subject:
mvenkatesha,
table RUSH_EMPLNO is parent, table RUSH_BEN_CLAIM is child.
Your foreign key is set from RUSH_EMPLNO.EMPNO (primary key of this table) to table RUSH_BEN_CLAIM.GOVTID with rule DELETE.
Your constraint is NOT set on column RUSH_EMPLNO.GOVTID, because this column is not primary key in your parent table.
Foreign key constraint connect primary key in parent table to any other column in child table and NOT any not primary key column in parent table with any columns in chold table.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
I was preparing for DB2 700 exam and surprised why I am getting foreign key constraint SQLCODE = -530. Can anyone please let me know what I am doing wrong because I feel record must be inserted but ending up in error? I am attaching the spufi output for your reference.
