title: Insert Row Into 2 Tables Linked w/Foregin Key Only If The Dependable Table's Insert Succeeds (Without Transaction)
body: <p>I have two MySQL tables and I need to insert a record in each of them. Caveat: The insert into dependable table may fail due to duplicate key conflict. In that case none of the rows should be inserted.</p>
  <pre><code>CREATE TABLE `meanings` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  
  CREATE TABLE `aliases` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `meaningId` int(10) unsigned DEFAULT NULL,
    `phrase` varchar(8) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `phrase_UNIQUE` (`phrase`),
    KEY `meaning_IDX` (`meaningId`),
    CONSTRAINT `fk_Synonyms_1` FOREIGN KEY (`meaningId`) REFERENCES `meanings` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB;
  </code></pre>
  <p>In a nutshell I need to do following but faster:</p>
  <pre><code>INSERT IGNORE INTO aliases (phrase, meaningId) VALUES ('word1', NULL);
  SELECT IF(row_count() &gt; 0, last_insert_id(), 0) INTO @aliasId;
  INSERT INTO meanings (id) SELECT null FROM aliases WHERE id=@aliasId;
  SELECT IFNULL(last_insert_id(), 0) INTO @meaningId;
  UPDATE aliases SET meaningId=@meaningId WHERE id=@aliasId;
  </code></pre>
  <p>My constraints/conditions/wishes:</p>
  <ol>
  <li>The SQL table scheme is given as above (although new cols can be added)</li>
  <li>Avoid using transactions</li>
  <li>Avoid using <code>DELETE FROM meanings</code></li>
  <li>Account for many processes doing the same thing simultaneously</li>
  <li>Optimize for mass insert of average of ~40 aliases at the time</li>
  <li>Minimize locks</li>
  <li>Records are permanent and will never be removed</li>
  </ol>
  <p>My thoughts:</p>
  <ul>
  <li>Comfortable way would be <code>trigger after insert</code> on <code>aliases</code> that inserts a row into <code>meanings</code> and updates back inserted row's <code>aliases.meaningId</code> but I am worried about performance</li>
  <li>In worst case I can do transactions if they are performant enough when compared to other solutions. This is a routine that may be called inside other transactions so avoiding transactions would be preferable.</li>
  <li>Performance on dozens of inserts at the time is the key factor (using PHP).</li>
  </ul>
  <p><strong>QUESTION</strong>: What is the fastest solution to insert ~40 (possibly conflicting) records into <code>aliases</code> at the time with corresponding records in <code>meaning</code> table?</p>
  <p>It seems that this use case scenario is so common that something back in my head keeps saying that I might lack some knowledge of existing trivial solutions/inbuilt support for this particular case that is both more elegant and more efficient (then the solution I came up with bellow).</p>
  <p>Note: The table is really 1:n even though in this example it behaves strictly 1:1. In my use case the data start their life cycle as 1:1 but later on their <code>meaning</code> can change...</p>
  <hr />
  <p><em>The example of desired result</em></p>
  <p>Before insert:</p>
  <pre><code>SELECT * FROM meanings;
  
  # id
    1 
    2 
    3 
  
  SELECT meaningId, phrase FROM aliases;
  
  # meaningId       phrase
    1               word1
    2               word2
    3               word3
  
  </code></pre>
  <p>Now I need to insert in highly optimized way values <code>word3</code> (note this one already exists), <code>word4</code>, <code>word5</code> so the result is this:</p>
  <pre><code>SELECT * FROM meanings;
  
  # id
    1 
    2 
    3 
    4
    5
  
  SELECT meaningId, phrase FROM aliases;
  
  # meaningId       phrase
    1               word1
    2               word2
    3               word3
    4               word4
    5               word5
  </code></pre>