MySQL: inserting records in tables linked by foreign key

Question

In MySQL, is it possible to have two tables linked by a foreign key and when a record is inserted into the parent table to create a corresponding record in the child table?

I have a website that handles member registrations. One table contains the member's details such as name and email address, and is linked to a settings table via member ID. What I would like is for a corresponding record to be entered into the settings table automatically when I create a new member. Is this possible? Or will that involve a stored procedure?

Greg K · Accepted Answer · 2009-11-18 11:41:23Z

4

I think what you might need is a trigger.

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

CREATE TRIGGER ins_settings AFTER INSERT ON members
  FOR EACH ROW BEGIN
    INSERT INTO settings SET member_id = NEW.member_id
    ...
  END;

edited Nov 18, 2009 at 11:41

answered Nov 18, 2009 at 11:29

Greg K

11.2k11 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Bean · Accepted Answer · 2009-11-19 15:05:44Z

1

Thanks to Greg, I managed to arrive at the solution, which is:

CREATE TRIGGER ins_settings AFTER INSERT ON members
FOR EACH ROW
INSERT INTO settings SET member_id = NEW.member_id;

answered Nov 19, 2009 at 15:05

Martin Bean

39.6k28 gold badges134 silver badges207 bronze badges

1 Comment

nickf Over a year ago

you can click on the tick just to the left of his answer to thank him properly :)

Collectives™ on Stack Overflow

MySQL: inserting records in tables linked by foreign key

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related