2

I have a table A and B in MARIADB. Please consider A as child of B and fk is ID. I am trying to insert value 15 to ID of A but a FK violation occurs. error is similar to given below:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f ails ("A", CONSTRAINT "FK_ID" FOREIGN KEY ("ID" ) REFERENCES "B" ("ID")).

Problem is that B got the value 15 in its ID. So, there is no need for error to occur. Then i dropped the fk constraint and tried to create again. This time, i was not able to create a fk constraint. Getting an error similar to given below:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f ails ("#sql-****_***", CONSTRAINT "FK_ID" FOREIGN KEY ("ID" ) REFERENCES "B" ("ID"))

I assume #sql-****_*** is a temporary table. Could anyone please tell me why this is happening?

Other useful info: datatype is decimal(15,0) engine is innodb.

SHOW CREATE TABLE A b4 dropping foreign key

Create Table: CREATE TABLE "A" (
  "MD" decimal(15,0) NOT NULL,
  "RE" varchar(25) COLLATE utf8mb4_bin DEFAULT NULL,
  "GD" decimal(15,0) DEFAULT NULL,
  "CK" varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
  "FC" varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  "TC" varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  "ME" varchar(25) COLLATE utf8mb4_bin DEFAULT NULL,
  "ID" decimal(15,0) DEFAULT NULL

  PRIMARY KEY ("MD"),
  UNIQUE KEY "UK_Y" ("TC","RE","ME","GD","FC"),
  KEY "FK_GD" ("GD"),
  KEY "FK_ID" ("ID"),
  CONSTRAINT "FK_GD" FOREIGN KEY ("GD") REFERENCES "D" ("MID"),

  CONSTRAINT "FK_ID" FOREIGN KEY ("ID") REFERENCES "B" ("ID")
)

SHOW CREATE TABLE B

Create Table: CREATE TABLE "B" (
  "ID" decimal(20,0) NOT NULL,
  "DE" varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
  "MY" varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
  "DDE" varchar(5) COLLATE utf8mb4_bin DEFAULT NULL,
  "CY" decimal(20,0) DEFAULT NULL,
  PRIMARY KEY ("ID"),
  UNIQUE KEY "UE" ("DE"),
  UNIQUE KEY "AE" ("DDE"),
  KEY "FK_CPY" ("CY"),
  CONSTRAINT "FK_CY" FOREIGN KEY ("CY") REFERENCES "X" ("MCY")
)

SHOW CREATE TABLE A after dropping FK

Create Table: CREATE TABLE "A" (
  "MD" decimal(15,0) NOT NULL,
  "RE" varchar(25) COLLATE utf8mb4_bin DEFAULT NULL,
  "GD" decimal(15,0) DEFAULT NULL,
  "CK" varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
  "FC" varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  "TC" varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  "ME" varchar(25) COLLATE utf8mb4_bin DEFAULT NULL,
  "ID" decimal(15,0) DEFAULT NULL

  PRIMARY KEY ("MD"),
  UNIQUE KEY "UK_Y" ("TC","RE","ME","GD","FC"),
  KEY "FK_GD" ("GD"),
  KEY "FK_ID" ("ID"),
  CONSTRAINT "FK_GD" FOREIGN KEY ("GD") REFERENCES "D" ("MID")
)

INSERT STATEMENT

INSERT  A (MD,
                                              RE,
                                              GD,
                                              CK,

                                              FC,
                                              TC,
                                              ME,
                                              ID
                                              )
                       VALUES (866,
                               'FILE',
                               NULL,
                               '',
                               '',
                               '',
                                '',
                               15
                              );

ORIGINAL show create table of child table

Create Table: CREATE TABLE `swift_mail_box` (
  `MAILBOX_ID` decimal(15,0) NOT NULL,
  `RECEIVE_MODE` varchar(25) COLLATE utf8mb4_bin DEFAULT NULL,
  `GATEWAY_IN_ID` decimal(15,0) DEFAULT NULL,
  `COMPLIANCE_CHECK` varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
  `FROM_BIC` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `TO_BIC` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `MSG_TYPE` varchar(25) COLLATE utf8mb4_bin DEFAULT NULL,
  `OWNER_ID` decimal(15,0) DEFAULT NULL,
  `FUNC_ID` decimal(15,0) DEFAULT NULL,
  `FILTER_CHECK` varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
  `CLIENT_ID` decimal(20,0) DEFAULT NULL,
  `COUNTERPARTY_ID` decimal(20,0) DEFAULT NULL,
  `DISPLAY_PAGE` varchar(5) COLLATE utf8mb4_bin DEFAULT NULL,
  `CREATE_NEW` varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
  `CBR_ID` decimal(20,0) DEFAULT NULL,
  `DEPT_ID` decimal(20,0) DEFAULT NULL,
  PRIMARY KEY (`MAILBOX_ID`),
  UNIQUE KEY `UK_SWIFT_MAIL_BOX` (`TO_BIC`,`RECEIVE_MODE`,`MSG_TYPE`,`GATEWAY_IN
_ID`,`FROM_BIC`,`CBR_ID`),
  KEY `FK_SWF_MAIL_GATEWAY_IN_ID` (`GATEWAY_IN_ID`),
  KEY `FK_SWIFT_MAIL_CBR_ID` (`CBR_ID`),
  KEY `FK_SWIFT_MAIL_COUNTERPARTY_ID` (`COUNTERPARTY_ID`),
  KEY `FK_SWIFT_MAIL_CLIENT_ID` (`CLIENT_ID`),
  KEY `FK_FUNCTION_ID` (`FUNC_ID`),
  KEY `FK_SW_MAIL_BOX_DEPT_ID` (`DEPT_ID`),
  CONSTRAINT `FK_FUNCTION_ID` FOREIGN KEY (`FUNC_ID`) REFERENCES `env_functions`
 (`FUNC_ID`),
  CONSTRAINT `FK_SWF_MAIL_GATEWAY_IN_ID` FOREIGN KEY (`GATEWAY_IN_ID`) REFERENCE
S `connect_source_link` (`MRM_MDM_ID`),
  CONSTRAINT `FK_SWIFT_MAIL_CBR_ID` FOREIGN KEY (`CBR_ID`) REFERENCES `content_b
ased_routing` (`CBR_ID`),
  CONSTRAINT `FK_SWIFT_MAIL_CLIENT_ID` FOREIGN KEY (`CLIENT_ID`) REFERENCES `cli
ents` (`CLIENT_ID`),
  CONSTRAINT `FK_SWIFT_MAIL_COUNTERPARTY_ID` FOREIGN KEY (`COUNTERPARTY_ID`) REF
ERENCES `counterparties` (`COUNTERPARTY_ID`),
  CONSTRAINT `FK_SW_MAIL_BOX_DEPT_ID` FOREIGN KEY (`DEPT_ID`) REFERENCES `depart
ments` (`DEPT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.000 sec)

Original show create of parent table

    Create Table: CREATE TABLE `corporate_divisions` (
  `OWNER_ID` decimal(20,0) NOT NULL,
  `DIVISION_NAME` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
  `MSG_CATEGORY` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
  `DIVISION_CODE` varchar(5) COLLATE utf8mb4_bin DEFAULT NULL,
  `EMAIL` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  `HIDDEN` varchar(5) COLLATE utf8mb4_bin DEFAULT NULL,
  `CATEGORY` decimal(20,0) DEFAULT NULL,
  PRIMARY KEY (`OWNER_ID`),
  UNIQUE KEY `UK_CORP_DIV_NAME` (`DIVISION_NAME`),
  UNIQUE KEY `UK_CORP_DIV_CODE` (`DIVISION_CODE`),
  KEY `FK_COP_DIV_CATEGORY_ID` (`CATEGORY`),
  CONSTRAINT `FK_COP_DIV_CATEGORY_ID` FOREIGN KEY (`CATEGORY`) REFERENCES `messa
ge_category_list` (`MSG_CATEGORY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.000 sec)

Version of Mariadb used:10.3.13-MariaDB OS: Windows Server 2012 R2 Standard

closed as off-topic by mustaccio, Marcello Miorelli, Michael Green, LowlyDBA, Colin 't Hart Aug 16 at 10:04

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, Marcello Miorelli, Michael Green, LowlyDBA, Colin 't Hart
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    So you haven't dropped the FK constraint. And why is the b.ID column decimal(20) while the a.ID is decimal(15)? – ypercubeᵀᴹ Aug 14 at 10:05
  • Hi, actually that didn't exist. I have manually added that one bec the real problem started when i tried to insert into A. I will edit the question once more. – Saivishnu S R Aug 14 at 10:08
  • 1
    Added original statements as well as version of mariadb and os – Saivishnu S R Aug 14 at 11:17
  • 1
    Thank you, good. Can you try changing that OWNER_ID to match the decima(20) of the referenced column (and then recreate the FK)? If that doesn't fix it, then it might be a bug or setup issue with MariaDB on Windows. – ypercubeᵀᴹ Aug 14 at 11:24
  • 1
    Thank you, Problem is solved when i changed datatype of child table's OWNER_ID to match the decimal(20,0) of the referenced column . – Saivishnu S R Aug 14 at 11:49

Browse other questions tagged or ask your own question.