How to give an INSERT of primary keys of an X table in a table Y

Question

How to give an INSERT of primary keys of an X table in a table Y

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

Hello, well, I'm having a hard time giving insert to the table, and I have two FK's that link the person table. My question is: How do I get the desired PK's and insert them as FK's?

Split 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)
);

Person table:

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!

mysql sql

asked by anonymous 05.11.2016 / 14:01

2 answers

0

You can pick up the inserted codes while including 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 another table

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

05.11.2016 / 14:55

Save data from a Game in Html5 and Javascript [closed] Problems in a Select CodeIgniter + PHP

score 1 · Accepted Answer

Well, I got what I wanted!

Here's my solution:

I created a function that when typing cpf (column document in person) it would return the id of the person itself, and gave an insert with that function.

(I did this from cpf to make it more intuitive to 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);