Insert with three tables in MySQL with PHP

Question

Insert with three tables in MySQL with PHP

Navigation

#1 by (2 votes)

1

For purposes of study and testing, I have created three tables in phpMyAdmin: People , Contact , CredentialsLogin . The Contact tables and CredentialsLogin are related to People.

The goal is to perform the insertion of records for the three tables. For this I have 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 that inserted in the previous insert.

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 could I insert these three tables at the same time?

Just remembering that I'm not worried right now with any kind of normalization of tables. This project was only for studies.

mysql sql

asked by anonymous 02.01.2019 / 20:13

1 answer

Application closing (unfortunately MyApplication has stopped) Difficulty in positioning div in CSS

score 2 · Accepted Answer

You can solve this problem in two ways; the most practical would be to query the last 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 does not remap 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;