How to insert primary keys from an X table into a Y table

Question

Hello, good, I’m having a hard time giving insert in the table divide, being that in it I have two FK’s that link the table person.. My question is the following: How to get the desired PK’s and insert as FK’s?

Divide table:

create table divida(
codigo_divida int(5) primary key auto_increment,
credor int(5) not null,
foreign key (credor) references pessoa(id_cliente),
data_atualizacao date not null,
valor_divida float not null,
devedor int(5) not null,
foreign key (devedor) references pessoa(id_cliente)
);

Table person:

create table pessoa
(id_cliente int(5) primary key auto_increment,
nome_cliente varchar(45) not null,
tipo varchar(10) not null,
telefone varchar(20) null,
documento varchar(25) not null,
endereco int(5),
foreign key (endereco) references endereco(endereco_id),
e_mail varchar(45) null,
unique(documento, e_mail)
);

Any help will be good life, thank you all!

Answer 1

Well, I got what I wanted!

Here is my solution:

I created a function that when typing Cpf (document column in person) it returns the id of the person itself, and I gave an input with this function.

(I did it from Cpf to become more intuitive for the user)

Function:

delimiter $$
create function executaBusca(doc varchar(25)) 
returns int
deterministic
begin
    declare idBuscado int;
    set idBuscado = (select id_cliente from pessoa where documento = doc);
    return idBuscado;
end $$
delimiter ;

Insert:

insert into divida (codigo_divida, credor, devedor, valor_divida) values (default, executaBusca("valor do cpf"), executaBusca("valor do cpf"), 1555.00);

Answer 2

You can take the inserted codes while include in the table using LAST_INSERT_ID. For example:

INSERT INTO pessoa ...

SET @id_cliente = LAST_INSERT_ID();

And then use the variables that were obtained to insert into the other table

INSERT INTO divida(id_cliente , ...) values(@id_cliente , ...)