How can i insert value into a table with foreign key?

Question

I have this scheme for tables

And i can't insert any value into a second or 3d table at all becouse of error 1452 (cannot add or update a child row.) How can i insert something? (not useing a SET FOREIGN_KEY_CHECKS=0;)

Answer 1

If you have cyclic foreign keys reference than you cannot insert data directly - insertion into any table fails due to foreign key violation, and insertion in more than one table per one query not supported.

The solution: insert into first table but set referencing column to NULL, insert into second table, update first table.

Example:

CREATE TABLE test1 (t1_id INT PRIMARY KEY, t2_id INT);
CREATE TABLE test2 (t1_id INT, t2_id INT PRIMARY KEY);
ALTER TABLE test1 ADD FOREIGN KEY (t2_id) REFERENCES test2 (t2_id);
ALTER TABLE test2 ADD FOREIGN KEY (t1_id) REFERENCES test1 (t1_id);

INSERT INTO test1 VALUES (1,11);

Cannot add or update a child row: a foreign key constraint fails (`db_262184466`.`test1`, CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`t2_id`) REFERENCES `test2` (`t2_id`))

INSERT INTO test2 VALUES (1,11);

Cannot add or update a child row: a foreign key constraint fails (`db_262184466`.`test2`, CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test1` (`t1_id`))

INSERT INTO test1 VALUES (1,NULL);
INSERT INTO test2 VALUES (1,11);
UPDATE test1 SET t2_id = 11 WHERE t1_id = 1;
SELECT * FROM test1; 
SELECT * FROM test2;

t1_id | t2_id
----: | ----:
    1 |    11

t1_id | t2_id
----: | ----:
    1 |    11

db<>fiddle here