2

Say i have 3 or 4 table which some table are connected with parent table. I want to submit data to these table at same time.

Table: table1
--------------------------------------------------------------------
| table1Id(AI)(PK) |  blah1 | blah2 | blah3 | blah3 | [....]
--------------------------------------------------------------------

Table: table2
--------------------------------------------------------------------
| table2Id(AI)(PK) | clah1 |  clah2 | clah3 | clah4 | [....] | table1Id (FK)
--------------------------------------------------------------------

Table: table3
--------------------------------------------------------------------
| table3Id(AI)(PK) | flah1 |  flah2 | flah3 | flah4 | [....] | table1Id (FK)
--------------------------------------------------------------------

And My database SQL Code is

CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
  `table1Id` INT NULL AUTO_INCREMENT,
  `blah1` VARCHAR(45) NULL,
  `blah2` VARCHAR(45) NULL,
  `blah3` VARCHAR(45) NULL,
  `blah4` VARCHAR(45) NULL,
  PRIMARY KEY (`table1Id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`table2` (
  `table2Id` INT NULL AUTO_INCREMENT,
  `clah1` VARCHAR(45) NULL,
  `clah2` VARCHAR(45) NULL,
  `clah3` VARCHAR(45) NULL,
  `clah4` VARCHAR(45) NULL,
  `table1Id` INT NULL,
  PRIMARY KEY (`table2Id`),
  INDEX `FK_table1_table2_idx` (`table1Id` ASC),
  CONSTRAINT `FK_table2_table1`
    FOREIGN KEY (`table1Id`)
    REFERENCES `mydb`.`table1` (`table1Id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`table3` (
  `table3Id` INT NULL AUTO_INCREMENT,
  `flah1` VARCHAR(45) NULL,
  `flah2` VARCHAR(45) NULL,
  `flah3` VARCHAR(45) NULL,
  `flah4` VARCHAR(45) NULL,
  `table1Id` INT NULL,
  PRIMARY KEY (`table3Id`),
  INDEX `FK_table3_table1_idx` (`table1Id` ASC),
  CONSTRAINT `FK_table3_table1`
    FOREIGN KEY (`table1Id`)
    REFERENCES `mydb`.`table1` (`table1Id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

SQL INSERT QUERY (Updated):

INSERT INTO table1 (table1Id, blah1, blah2, blah3, blah4, blah5)
     VALUES ('', NULL, NULL, NULL, NULL, NULL);

INSERT INTO table2 (clah1, clah2, clah3, clah4, clah5, table1Id)
     VALUES (NULL, NULL, NULL, NULL, NULL, LAST_INSERT_ID());

INSERT INTO table3 (flah1, flah2, flah3, flah4, flah5, table1Id)
     VALUES (NULL, NULL, NULL, NULL, NULL, LAST_INSERT_ID());

This query shows me error which is:

1452 - Cannot add or update a child row: a foreign key constraint fails

But if insert only 2 table then works okay

INSERT INTO table1 (table1Id, blah1, blah2, blah3, blah4, blah5)
     VALUES ('', NULL, NULL, NULL, NULL, NULL);

INSERT INTO table2 (clah1, clah2, clah3, clah4, clah5, table1Id)
     VALUES (NULL, NULL, NULL, NULL, NULL, LAST_INSERT_ID());
Tawhidul Islam
  • 352
  • 4
  • 19
  • 1
    LAST_INSERT_ID() returns the last auto generated id. table1id is not autogenerated it is hardcoded as 101. The LAST_INSERT_ID() value is most probably some other value that does not exist in table1.table1Id, thats why you got the error. Just use your explicit table1Id value for table2,table3 – Daniel PP Cabral Mar 23 '16 at 08:13
  • Possible duplicate of [Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails](http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa) – rhavendc Mar 23 '16 at 08:17
  • Okay, table1Id is auto increment , if i don't input table1Id value then how i will get table1Id running insert value ? @Daniel PP Cabral – Tawhidul Islam Mar 23 '16 at 08:49

1 Answers1

0

try 

INSERT INTO table1 (table1Id, blah1, blah2, blah3, blah4, blah5)
     VALUES (101, NULL, NULL, NULL, NULL, NULL);

INSERT INTO table2 (clah1, clah2, clah3, clah4, clah5, table1Id)
     VALUES (NULL, NULL, NULL, NULL, NULL, 101);

INSERT INTO table3 (flah1, flah2, flah3, flah4, flah5, table1Id)
     VALUES (NULL, NULL, NULL, NULL, NULL, 101);
Oleg Liski
  • 563
  • 4
  • 15