Insert with three tables in Mysql with PHP

Question

For study and testing purposes, I created three tables in phpMyAdmin: People, Contact, Credentiallogin. Contact and Credentiallogin tables are related to People.

The goal is to perform the insertion of records for the three tables. For this I created the following query:

START TRANSACTION;
INSERT INTO pessoas VALUES (NULL, 'TESTE','[email protected]','2019-01-08','masc','TESTE'); 
INSERT INTO contato VALUES (LAST_INSERT_ID(), '33333333','Rua teste, 57','Catanduva','SP','18353-251');
INSERT INTO credenciaislogins VALUES (LAST_INSERT_ID(), 'admin','admin');
COMMIT;

Where the value to be returned from LAST_INSERT_ID() is the same as the one that was inserted in the previous Sert.

However, when executing the query the following error is generated

Comando SQL:

INSERT INTO contato VALUES (LAST_INSERT_ID(), '33333333','Rua teste, 57','Catanduva','SP','18353-251')
Mensagens do MySQL : Documentação

#1452 - Cannot add or update a child row: a foreign key constraint fails (`db_testephp`.`contato`, CONSTRAINT `contato_ibfk_1` FOREIGN KEY (`ID_PESSOA`) REFERENCES `pessoas` (`IDPESSOA`))

How I could make the Insert in these three tables at the same time?

Just remembering that I am not concerned at this time with any kind of normalization of the tables. This project is only for studies.

Answer 1

You can solve this problem in two ways; the most practical would be to consult the latter id inserted at the time of the next insert:

START TRANSACTION;
  INSERT INTO pessoas VALUES (NULL, 'TESTE','[email protected]','2019-01-08','masc','TESTE'); 
  INSERT INTO contato VALUES ((SELECT MAX(id) FROM pessoas), '33333333','Rua teste, 57','Catanduva','SP','18353-251');
  INSERT INTO credenciaislogins VALUES ((SELECT MAX(id) FROM pessoas), 'admin','admin');
COMMIT;

The second, more performative since not remake queries, would create a variable to store this value:

START TRANSACTION;
  SET @id:= (SELECT MAX(id) + 1 FROM tabela1);
  INSERT INTO pessoas VALUES (@id, 'TESTE','[email protected]','2019-01-08','masc','TESTE'); 
  INSERT INTO contato VALUES (@id, '33333333','Rua teste, 57','Catanduva','SP','18353-251');
  INSERT INTO credenciaislogins VALUES (@id, 'admin','admin');
COMMIT;