Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
in case of multiple triggers executing on multiple tables to fill one defined table, taking in mind all default values are set correctly. can you help me with an example. +1, on your answer :) –  mamdouh alramadan Oct 15 '13 at 21:33
    
If you have a question that is related to, but not the same as, this one, you should probably post it as a different question, and, you should probably link to this question including an explanation of why your question is not a duplicate. I suspect, though, that the answer to what I think you might be asking would involve using INSERT ... ON DUPLICATE KEY UPDATE to leave any existing values there, if the row exists, and insert a new row if it doesn't already exist. –  Michael - sqlbot Oct 15 '13 at 21:44
    
thanks for your fast response, and as you said, it is better to post a different question :) –  mamdouh alramadan Oct 15 '13 at 21:47
    
I added this question –  mamdouh alramadan Oct 15 '13 at 22:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.