65687

Fill MySQL records one-to-many related tables in one action

Question:

I have two MySQL tables with an one-to-many relationship between them. For example:

CREATE TABLE test1 ( pk1 INTEGER AUTO_INCREMENT PRIMARY KEY, testvalue1 INTEGER ); CREATE TABLE test2 ( pk2 INTEGER AUTO_INCREMENT PRIMARY KEY, testvalue2 VARCHAR(50), fk2 INTEGER NOT NULL, FOREIGN KEY (fk2) REFERENCES test1 (pk1) );

If I want to insert records in both tables I can first insert a record in the PK table (e.g. INSERT INTO test1 SET testvalue1=100), determine the PK value (e.g. SELECT MAX(pk1) AS LastId FROM test1 or use LAST_INSERT_ID()) and finally use that value to fill the FK column in the second table.

But is it possible to achieve this all in 1 command/query/action? So let's MySQL fill in the PK- and FK-values using the AUTO INCREMENTs?

Answer1:

You should use two INSERT commands; or try to use an INSERT-trigger.

<strong>EDIT:</strong>

--An example with trigger:

CREATE TABLE dept( id INT(11) NOT NULL AUTO_INCREMENT, dept_name VARCHAR(255) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE emp( id INT(11) NOT NULL AUTO_INCREMENT, emp_name VARCHAR(255) DEFAULT NULL, dept_id INT(11) DEFAULT NULL, PRIMARY KEY (id), CONSTRAINT FK_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept (id) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = INNODB; DELIMITER $$ CREATE TRIGGER trigger1 AFTER INSERT ON dept FOR EACH ROW BEGIN INSERT INTO emp VALUES (NULL, 'Someone', NEW.id); END $$ DELIMITER ; -- Try to add new department. INSERT INTO dept VALUES(NULL, 'Sales'); -- Is there new default employee? SELECT * FROM emp; +----+----------+---------+ | id | emp_name | dept_id | +----+----------+---------+ | 1 | Someone | 1 | +----+----------+---------+

Recommend

  • MySQL performance using AUTO_INCREMENT on a PRIMARY KEY
  • Pointer vs Reference difference when passing Eigen objects as arguments
  • jinja2 template not found and internal server error
  • LNK1104: cannot open file 'kernel32.lib'
  • Many to Many in Linq using Dapper
  • Extending the Django 1.11 User Model
  • How to Model Ternary Relationship in CakePhp?
  • Are there any side effects from calling SQLAlchemy flush() within code?
  • SQL: Getting the physical size of a subset of a table
  • Can my PDF ping my server when it is opened?
  • Find JSON nested nodes using multiple string values
  • How do I remove all but some records based on a threshold?
  • User messaging system
  • How do I get the list of bad records that didn't load in Bigquery?
  • Pycharm: Marking a folder as 'sources root' is not recursive for subfolders
  • Adding elements to a huge XML file
  • close() was never explicitly called on database
  • Eliminate partial duplicate rows from result set
  • Sequential (transactional) API calls in angular 4 with state management
  • How can the INSERT … ON CONFLICT (id) DO UPDATE… syntax be used with a sequence ID?
  • Marklogic : Query response time is very high
  • Transactional Create with Validation in ServiceStack Redis Client
  • Why querying a date BC is changed to AD in Java?
  • Record samples being played with OpenAL
  • Retrieving value from sql ExecuteScalar()
  • PHP - How to update data to MySQL when click a radio button
  • How to convert from System.Drawing.Color to Excel.ColorFormat in C#? Change comment color
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • Delete MySQLi record without showing the id in the URL
  • Properly structure and highlight a GtkPopoverMenu using PyGObject
  • Unanticipated behavior
  • Comma separated Values
  • Matrix multiplication with MKL
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • How to set the response of a form post action to a iframe source?
  • Trying to get generic when generic is not available
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • reshape alternating columns in less time and using less memory