CODEWITHSUNDEEP
mysqlsql
dronikk
dronikk

Reputation: 39

Create record in on table after creating record in second table

Here is how my ERD looks like

enter image description here

I have users, that have autoincremented ID. When new user is added, there is trigger which inserts into biodata (in biodata table will be later info about user DOB, city etc.) new record. Biodata user_id is also autoincremented and I am using following trigger to fill it whenever new user is added:

delimiter $$
create trigger trigger_biodata
after insert 
on user for each row
BEGIN
    insert into biodata values(null);
END $$
delimiter ;

But there is an error when user insert fails saying:

insert into user values(null,'biesiek','to','faja') Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (mydb.biodata, CONSTRAINT fk_biodata_user2 FOREIGN KEY (user_id) REFERENCES user (id))

So I am looking for info how to fix that or how to make possible to make second table getting same user_id whenever new user is added.

Upvotes: 1

Views: 41

Answers (1)

Akina
Akina

Reputation: 42622

CREATE TRIGGER tr_ai_biodata
AFTER INSERT 
ON user 
FOR EACH ROW
INSERT INTO biodata (user_id) VALUES (NEW.id);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c265d6f2703052e5b1a806c6d270936e

Upvotes: 2

Related Questions