Question :
I have a users table that has a one to one relationship to a user_preferences table (primary foreign key user_id). When a new row is added to the users table (new id added), is there a way to setup the relationship between the users_preferences table so that a row with the new user id is also added to it?
Answer :
Calling this a way to “setup the relationship” isn’t quite technically accurate, but you can do this with an AFTER INSERT trigger on the user table. If all of the other columns in user_preferences have appropriate default values defined, and creating a “matching” row is all you need, then:
mysql> CREATE TRIGGER user_ai AFTER INSERT ON `user` FOR EACH ROW INSERT INTO user_preferences (user_id) VALUES (NEW.user_id);
http://dev.mysql.com/doc/refman/5.5/en/triggers.html
Triggers that execute more than one statement require you to temporarily change the DELIMITER from the normal semicolon to something else, and use an outer BEGIN and END block. This also works with a single-statement trigger, but isn’t strictly necessary, so the format above will work in this case.