Foreign key violation when trying to insert into a table

by Saivishnu S R   Last Updated August 14, 2019 10:06 AM

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

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")
)


Related Questions




MariaDB: disable foreign key checks

Updated August 10, 2016 08:04 AM


Am i doing anything wrong with "foreign key" here?

Updated August 12, 2017 13:06 PM