CODEWITHSUNDEEP
phpmysqlrelational
Martin Bean
Martin Bean

Reputation: 39389

MySQL: inserting records in tables linked by foreign key

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?

Upvotes: 0

Views: 431

Answers (2)

Martin Bean
Martin Bean

Reputation: 39389

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;

Upvotes: 1

Greg K
Greg K

Reputation: 11120

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;

Upvotes: 4

Related Questions