Referential Integrity Errors
If you encounter a Referential Integrity (RI) error while updating or inserting a row, File-AID for Db2 assists you in resolving the error without leaving your edit session.
The department table (DEPT) is the parent table and the employee table (EMP) is the dependent table. The DEPTNO column in the DEPT table is the primary key. The WORKDEPT column in the EMP table is a foreign key; it refers to the DEPTNO column. Every WORKDEPT number in the employee table must exist in the department table. The following table shows the relationship.
Sample Relationship
Parent Table | Primary Key | Dependent Table | Foreign Key |
|---|---|---|---|
DEPT | DEPTNO | EMP | WORKDEPT |
Inserting a Row into the Employee Table
-------------------------------------------------------------------------------
File-AID for DB2 EDIT - USERID1.EMP SSID: DSN
COMMAND ===> SCROLL ===> CSR
EMPNO FIRSTNAME MIDINIT LASTNAME WORKDEPT PHONENO
CHAR(6) VC(12) CHAR(1) VC(15) CHAR(3) CHAR(4)
PRIMARY ----------- ------- --------------- -------- --------
****** ***************************** TOP OF DATA ******************************
000001 000010 CHRISTINE I HAAS A00 3978
000002 000020 MICHAEL L THOMPSON B01 3476
000003 000030 SALLY A KWAN C01 4738
000004 000050 JOHN B GEYER E01 6789
000005 000060 IRVING F STERN D11 6423
000006 000061 JANE R SMITH E12 7618
000007 000070 EVA D PULASKI D21 7831
000008 000090 EILEEN W HENDERSON E11 5498
000009 000100 THEODORE Q SPENSER E21 9720
000010 000110 VINCENZO G LUCCHESI A00 3490
000011 000120 SEAN O O’CONNELL A00 2167
000012 000130 DOLORES M QUINTANA C01 4578
000013 000140 HEATHER N NICHOLSON D11 4510
000014 000160 BRUCE A ADAMSON D11 4510
000015 000170 ELIZABETH R PIANKA D11 3290
000016 000180 MASATOSHI J YOSHIMURA D11 2890
****** **************************** BOTTOM OF DATA ****************************
Inserting a Row into the Employee Table shows a new row being inserted into the employee table. The RI Relationship Error window (RI Relationship Error Window) displays when File-AID for Db2 encounters a -530 referential integrity error.
RI Relationship Error Window
RI Relationship Error Window shows an SQL error because the WORKDEPT entered is not a department in the parent table, DEPT. The row in error moves to the top of the display and is flagged with ==ERR> in the line command area. The window displays the Db2 error message and code. You can also enter the maximum number of rows to select. The column and data in error are identified. You can issue the UP and DOWN commands to view up to 64 foreign key columns. The first 40 characters of the Data in Error field are displayed to identify the problem. Data beyond this limit is not shown.
To resolve the RI error, you can do one of the following:
- Press Enter on this screen to browse the parent table and select the correct data. See RI Browse Window for more information.
- Enter END to exit the window and return to the edit session. Then, you can edit the parent table by issuing the EDIT primary command and add the correct data. See Editing-Multiple-Unrelated-Tables for more information.
RI Browse Window
Once the RI Browse window displays, you can resolve the RI error by selecting a row from the parent table. When you select a row, the data in the primary key column (DEPTNO) replaces the data in the dependent table’s foreign key column (WORKDEPT). See Resolving RI Errors in Table Mode to resolve the error in table mode. See Resolving RI Errors in Row Mode to resolve the error in row mode.
You can only issue standard browse mode commands from the RI Browse window. You cannot issue Related Table Edit commands (REDIT, RBROWSE, JUMP, MAX, and MIN).
Primary key columns are left justified and highlighted.
Resolving RI Errors in Table Mode
Use the RI line command to select the row containing the desired column data.
RI Browse Window — Using the RI Line Command
RI Browse Window — Using the RI Line Command shows the user selecting department number E21 by typing RI in the line number area. Press Enter to automatically replace the data in error, E12, with the new work department, E21. The following figure shows the RI error resolved.
RI Error Resolved — Table Mode
-------------------------------------------------------------------------------
File-AID for DB2 EDIT - USERID1.EMP SSID: DSN
COMMAND ===> SCROLL ===> CSR
EMPNO FIRSTNAME MIDINIT LASTNAME WORKDEPT PHONENO
CHAR(6) VC(12) CHAR(1) VC(15) CHAR(3) CHAR(4)
PRIMARY ----------- ------- --------------- -------- --------
000006 000061 JANE R SMITH E21 7618
000007 000070 EVA D PULASKI A00 7831
000008 000090 EILEEN W HENDERSON E11 5498
000009 000100 THEODORE Q SPENSER E21 9720
000010 000110 VINCENZO G LUCCHESI A00 3490
000011 000120 SEAN O O’CONNELL A00 2167
000012 000130 DOLORES M QUINTANA C01 4578
000013 000140 HEATHER A NICHOLS C01 1793
000014 000160 BRUCE A ADAMSON D11 4510
000015 000170 ELIZABETH R PIANKA D11 3782
000016 000180 MASATOSHI J QUINTANA C01 4578
000017 000190 MARILYN S SCOUTTEN D11 1682
000018 000200 PETER S ROSE D11 3782
000019 000210 ELIZABETH R PIANKA D11 3782
000020 000220 MASATOSHI J YOSHIMURA D11 1682
000021 000220 MARILYN S SCOUTTEN D11 1682
Once you have resolved the referential integrity error, you can continue your edit session.
Resolving RI Errors in Row Mode
Use the RI primary command to select the row containing the desired column data.
RI Browse Window — Entering the RI Primary Command
RI Browse Window — Entering the RI Primary Command shows the user selecting department number E21 by typing RI in the COMMAND field. Press Enter to automatically replace the data in error, E12, with the new work department, E21. The following figure shows the RI error resolved.
RI Error Resolved — Row Mode
-------------------------------------------------------------------------------
File-AID for DB2 Edit - USERID1.EMP SSID: DSN
COMMAND ===> SCROLL ===> CSR
Row 1
COLUMN NAME TYPE(LEN) KEY COLUMN VALUE
--- ----------- ---------- --- ---+---10---+---20---+---30---+---40
005 WORKDEPT CHAR(3) E21
006 PHONENO CHAR(4) 3978
007 HIREDATE DATE 1965-01-01
008 JOB CHAR(8) PRES
009 EDLEVEL SMALLINT 18
010 SEX CHAR(1) F
011 BIRTHDATE DATE 1933-08-14
012 SALARY DEC(9,2) 9999999.00
013 BONUS DEC(9,2) 1000.00
014 COMM DEC(9,2) 4220.00
*********************** BOTTOM OF DATA **********************************
F10=PREV F11=NEXT
Once you have resolved the referential integrity error, you can continue your edit session.
Resolving Multiple Errors
If multiple RI errors are flagged, File-AID for Db2 displays the RI Relationship Error window (RI Relationship Error Window) for every error encountered. That is, after resolving the first error, it reappears whenever you attempt to scroll, enter a command, or press Enter. To resolve the error, you can:
- Press Enter to display the RI Browse window and issue the RI line or primary command.
- Enter END to return to the edit session and type over the data.
- Enter END to return to the edit session and issue a CHANGE or CANCEL command.
Related topics