Background

Wanted to go over an error I self generated so I can see the options available in Db/2 to address it.

Error

Error Image

Error Text

SQL Error [23503]: The insert or update value of the FOREIGN KEY “bank.transaction.FK_bank_transaction_account” is not equal to any value of the parent key of the parent table.. SQLCODE=-530, SQLSTATE=23503, DRIVER=4.21.29

Generate Error

Let us create the SQL Objects and trigger the problem.

Objective

We will have two tables, account and transactions.

The account’s table has a primary key, PK_bank_account.

The transaction’s table will have a primary key, PK_bank_transaction, as well.

The primary key will be a composite key on two columns, accountID and dt.

The transaction’s table will also have a Foreign Key, FK_bank_transaction_account.

The foreign key will reference the account table.

Our requirement is pretty basic each record in the transaction table must have a anchoring record in the Account’s table.

Data Model

Here is our Data Model

DDL

account

CREATE TABLE "bank"."account"
(

	  "accountID"  		varchar(11) NOT NULL

	, "accountHolder"	varchar(60) NOT NULL

	,  "dtCreated"      TIMESTAMP   NOT NULL

)
//

ALTER TABLE  "bank"."account"

	ALTER COLUMN "dtCreated"

	SET DEFAULT CURRENT TIMESTAMP

//

ALTER TABLE "bank"."account"
ADD CONSTRAINT "PK_bank_account"
     PRIMARY KEY
     (
     	"accountID"
 	 )
//

transaction

CREATE TABLE "bank"."transaction"
(

	  "accountID"  		varchar(11)   NOT NULL

	, "dt"      		TIMESTAMP     NOT NULL

	, "amount"	        decimal(30,4) NOT NULL

)
//

ALTER TABLE  "bank"."transaction"

	ALTER COLUMN "dt"

	SET DEFAULT CURRENT TIMESTAMP

//

ALTER TABLE "bank"."transaction"
ADD CONSTRAINT "PK_bank_transaction"
     PRIMARY KEY
     (
     	   "accountID"
		,  "dt"
     )
//

ALTER TABLE "bank"."transaction"
ADD CONSTRAINT "FK_bank_transaction_account"
     FOREIGN KEY
     (
     	   "accountID"
     )
REFERENCES "bank"."account"

//

DML

Add a Transaction

SQL

INSERT INTO "bank"."transaction"
(

	  "accountID"
	, "dt"
	, "amount"

)
VALUES
	(
		  301
		, CURRENT_TIMESTAMP + 10 SECOND
		, 10.00
	)

//

Output

Output – Image

Output – Textual

SQL Error [23503]: The insert or update value of the FOREIGN KEY “bank.transaction.FK_bank_transaction_account” is not equal to any value of the parent key of the parent table.. SQLCODE=-530, SQLSTATE=23503, DRIVER=4.21.29

Output – Explanation

The key verbiage is the the one we are highlighted.

It reads that the entry we are trying to add does not have any value in the parent key of the parent table.

Remediation

Set Foreign Key to “Not Enforced”

Snippet

SQL

SQL – Syntax

ALTER TABLE [schema].[table]
     ALTER FOREIGN KEY [foreign-key]
     NOT ENFORCED
    ;

SQL – Sample

ALTER TABLE "bank"."transaction"
 	ALTER FOREIGN KEY "FK_bank_transaction_account"
 	NOT ENFORCED

Complete

Outline

1. Alter Table – Alter Foreign Key – Not Enforced
2. Add data to referencing table
3. Add data to referenced table
4. Alter Table – Alter Foreign Key – Enforced

SQL

SQL – Syntax

ALTER TABLE [schema].[table]
     ALTER FOREIGN KEY [foreign-key]
     NOT ENFORCED
    ;

/*
    Add records to dependent table
*/

/*
    Add records to referenced table
*/

ALTER TABLE [schema].[table]
     ALTER FOREIGN KEY [foreign-key]
     ENFORCED
    ;

SQL – Sample

ALTER TABLE "bank"."transaction"
 	ALTER FOREIGN KEY "FK_bank_transaction_account"
 	NOT ENFORCED
//	

INSERT INTO "bank"."transaction"
(

	  "accountID"
	, "dt"
	, "amount"

)
VALUES
	(
		  301
		, CURRENT_TIMESTAMP + 10 SECOND
		, 10.00
	)

//

INSERT INTO "bank"."account"
(
	  "accountID"
	, "accountHolder"
	, "dtCreated"

)
VALUES
	(
		  '301'
		, 'Joanne Crarford'
		, CURRENT_TIMESTAMP + 1 SECONDS
	)

//

ALTER TABLE "bank"."transaction"
 	ALTER FOREIGN KEY "FK_bank_transaction_account"
 	ENFORCED
 //	

ROLLBACK
//

 

Flag “Un-Enforced Constraints”

SQL

SQL – Sample

 SELECT 

		  CONCAT
		  	(
		  	    CONCAT
			  	(
			  		  rtrim(tblSTC.TABSCHEMA)
			  		, '.'
				)
				
			    , rtrim(tblSTC.TABNAME)
		    )
		  	AS "table"
			
		, tblSTC.CONSTNAME
			AS "constraint"
			
		, tblSTC.TYPE
			AS "type"
			
		, tblSTC.ENFORCED
			AS "enforced"
			
		, tblSTC.TRUSTED
			AS "trusted"
			
		, tblSTC.CHECKEXISTINGDATA
			AS "checkExistingData"
	
FROM   SYSCAT.TABCONST tblSTC

WHERE
		(
			   ( tblSTC.ENFORCED = 'N' )
			OR ( tblSTC.TRUSTED  = 'N' )
			OR ( tblSTC.CHECKEXISTINGDATA = 'N' )

		)

 

SQL – Output