0

I have three tables in a MySQL database, the tables are labeled address, employee, and login_info. address and employee have a one to one identifying relationship with a foreign key with login_info table. I would like to write a single sql query that would populate all three tables at the same and use the PK from the login_info table as the foreign key in both the employee and address table. How would I go about doing this, below are the column descriptions for the three tables I have. Thanks in advance for any help or suggestion.

enter image description here

user3221699
  • 71
  • 1
  • 2
  • 8
  • Are you saying it is not possible, and I would have to insert to each table individually? – user3221699 Apr 27 '15 at 00:50
  • yep. not possible. see here for tips: http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization – pala_ Apr 27 '15 at 00:53

1 Answers1

0

You can try this structure, mate: 

START TRANSACTION;
-- INSERT BLOCK FOR login_info
-- GET THE NEEDED KEY FROM login_info LAST INSERT
-- INSERT BLOCK FOR employee
-- INSERT BLOCK FOR address
COMMIT;

This is not the single-query answer you are expecting, but this may satisfy your need for your module. Using MySQL's TRANSACTION makes it either everything is executed or nothing changes. The 'A' in the ACID characteristic of MySQL.

Avidos
  • 739
  • 12
  • 23
  • this is the same info provided in a link in the comments – pala_ Apr 27 '15 at 01:43
  • 1
    @pala_ Sort of, mate. I do not even claim this post to be original or 'mine' but this is a guide for the answer to this question. – Avidos Apr 27 '15 at 01:49