title: Error 1452 when updating a parent table using mariaDB
body: <p>I get error 1452 when updating a row in a parent table when running mariaDB 10.5.8. The tables are defined as follows:</p>
  <pre class="lang-sql prettyprint-override"><code>&gt; SHOW CREATE TABLE files;
  files   CREATE TABLE `files` (
    `file_path` varchar(255) NOT NULL,
    `md5` text NOT NULL,
    `size` int(11) NOT NULL,
    PRIMARY KEY (`file_path`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  </code></pre>
  <pre class="lang-sql prettyprint-override"><code>&gt; SHOW CREATE TABLE headlines;
  headlines   CREATE TABLE `headlines` (
    `file_path` varchar(255) NOT NULL,
    `headline_offset` int(11) NOT NULL,
    -- other columns
    PRIMARY KEY (`file_path`,`headline_offset`),
    CONSTRAINT `headlines_ibfk_1` FOREIGN KEY (`file_path`) REFERENCES `files` (`file_path`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  </code></pre>
  <pre class="lang-sql prettyprint-override"><code>&gt; SHOW CREATE TABLE headline_closures;
  headline_closures   CREATE TABLE `headline_closures` (
    `file_path` varchar(255) NOT NULL,
    `headline_offset` int(11) NOT NULL,
    `parent_offset` int(11) NOT NULL,
    `depth` int(11) DEFAULT NULL,
    PRIMARY KEY (`file_path`,`headline_offset`,`parent_offset`),
    KEY `file_path` (`file_path`,`parent_offset`),
    CONSTRAINT `headline_closures_ibfk_1` FOREIGN KEY (`file_path`, `headline_offset`) REFERENCES `headlines` (`file_path`, `headline_offset`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `headline_closures_ibfk_2` FOREIGN KEY (`file_path`, `parent_offset`) REFERENCES `headlines` (`file_path`, `headline_offset`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  </code></pre>
  <p>The contents of the database (minimal example):</p>
  <pre class="lang-sql prettyprint-override"><code>&gt; SELECT * FROM files;
  /path/to/foo1.org   106e9f12c9e4ff3333425115d148fbd4    6
  
  &gt; SELECT * FROM headlines;
  /path/to/foo1.org   1   --other columns
  
  &gt; SELECT * FROM headline_closures;
  /path/to/foo1.org   1   1   0
  </code></pre>
  <p>Executing the following produces the error (expected behavior is that the file path changes for all tables per the <code>CASCADE</code> clauses):</p>
  <pre class="lang-sql prettyprint-override"><code>&gt; UPDATE files SET file_path='/path/to/foo2.org' WHERE md5='106e9f12c9e4ff3333425115d148fbd4';
  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`org_sql`.`headline_closures`, CONSTRAINT `headline_closures_ibfk_2` FOREIGN KEY (`file_path`, `parent_offset`) REFERENCES `headlines` (`file_path`, `headline_offset`))
  </code></pre>
  <p>Things I've tried (per <a href="https://stackoverflow.com/questions/63600963/mysql-cascade-update-failed-when-foreign-key-column-referenced-in-composite-prim">MySQL cascade update failed when foreign key column referenced in composite primary key</a>)</p>
  <ol>
  <li>Removing the second <code>ON DELETE CASCADE ON UPDATE CASCADE</code> from the <code>headline_closures</code> table (same error)</li>
  <li>Dropping the primary key from the <code>headline_closures</code> table and that produced <code>error 150 (Foreign key constraint is incorrectly formed)</code></li>
  </ol>
  <p>I thought (1) would work because the docs state to not have multiple <code>CASCADE</code>s that could change a single column; I'm not sure why (2) would work. What am I not getting here?</p>
  <p>Also, if someone could explain why this same arrangement works for SQLite and postgreSQL that would be appreciated since I don't get the same error with those ;)</p>
  <h3>Edit 1</h3>
  <ul>
  <li>This same behavior happens with MySQL 8.0.22 (so not just a mariaDB error, not too surprising)</li>
  <li>I don't get the error if I take away one of the foreign keys in <code>headline_closures</code> (so there is still one constraint that has <code>ON UPDATE CASCADE</code>)</li>
  <li>If I remove the <code>ON UPDATE CASCADE</code> from <code>headline_closures_ibfk_1</code> instead of <code>headline_closures_ibfk_2</code>, the error then complains about <code>headline_closures_ibfk_1</code> (where it complained about <code>headline_closures_ibfk_2</code> when I took the <code>CASCADE</code>s away from <code>headline_closures_ibfk_2</code> or if I left them on both) so it seem that the error is due to updating the parent, having the cascade propagate to the <code>headline_closures</code> table where the second constraint doesn't see the update and therefore complains (or something)</li>
  </ul>
  <h3>Edit 2:</h3>
  <ul>
  <li><code>DELETE FROM files WHERE md5='106e9f12c9e4ff3333425115d148fbd4';</code> works as intended with the constraints as written above (no error and the the deletion propagates to all child tables).</li>
  </ul>