Enforce inserting records into multiple tables

Question

I have two tables parent and child. I would like to insert a record in both tables only if the query succeeds for both. Ideally, integrity would be enforced by the database, however, doing so using the application would be acceptable if necessary. Potentially, I could do something like the following, however, it seems like there would be a more elegant method to do so. How should this be accomplished?

<?php
//parent must be inserted first as child has a FK constraint to it
$stmt1=$conn->prepare('INSERT INTO parent(id,data) VALUES(?,?)');
if($stmt1->execute(array(123,"foo"))) {
    $stmt2=$conn->prepare('INSERT INTO child(parent_id,data) VALUES(?,?)');
    if(!$stmt2->execute(array(123,"bar"))) {
        $stmt3=$conn->prepare('DELETE FROM parent WHERE id=?');
        $stmt3->execute(array(123));
    }
}
?>

EDIT. Would something like the following work?

<?php

$queries=array(
    array('sql'=>'INSERT INTO parent(id,data) VALUES(?,?)',$data=>array(123,"foo")),
    array('sql'=>'INSERT INTO child(parent_id,data) VALUES(?,?)',$data=>array(123,"bar")),
    //More if necessary array('sql'=>'',$data=>array()),
);

doMultipleQueries($queries);

function doMultipleQueries($queries) {

    try {
        $conn->beginTransaction();
        foreach($queries as $query) {
            $stmt=$conn->prepare($query['sql']);
            $stmt->execute($query['data']);
        }
        $conn->commit();
        return true;
    } catch (Exception $e) {
        $conn->rollBack();
        return false;
    }
}
?>

Schema

CREATE TABLE IF NOT EXISTS `parent` (
  `id` INT NOT NULL,
  `data` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `child` (
  `parent_id` INT NOT NULL,
  `data` VARCHAR(45) NULL,
  PRIMARY KEY (`parent_id`),
  CONSTRAINT `fk_child_parent`
    FOREIGN KEY (`parent_id`)
    REFERENCES `parent` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

use a transaction as well. if something fails in an later query, you'll want to undo the earlier ones, you're going to end up with crap in the DB. — Marc B, Jan 07 '15 at 15:19
I was considering a trigger but wasn't sure, however, wasn't sure how that would be implemented if I had more than two related insert. Never worked with transactions before and will investigate. — user1032531, Jan 07 '15 at 15:21
If you use transactions dont forget `mysqli_multi_query` which I dont know if it accepts placeholder values — Mihai, Jan 07 '15 at 15:25
@Mihai As far as I could tell, `mysqli_multi_query` doesn't work with PDO. — user1032531, Jan 07 '15 at 15:28
Maybe PDO has some multi query library? It has http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — Mihai, Jan 07 '15 at 15:28
@Mihai Maybe some libraries, but why is it necessary? I am going to put an initial script together in the next 3 minutes and add it to my original, and hope you could take a quick look — user1032531, Jan 07 '15 at 15:33
@Mihai Okay, 6 minutes, not 3 minutes. Where would the multi-query part be required? Does what I show make any sense? Thank you — user1032531, Jan 07 '15 at 15:40
Might work cant tell,just test it.Doesnt work ask a new question. — Mihai, Jan 07 '15 at 15:41

score 2 · Accepted Answer · answered Jan 07 '15 at 15:18

2

you should look into database transactions, they will role back all actions if one of them fails

answered Jan 07 '15 at 15:18

MadDokMike

182
5

score 2 · Answer 2 · answered Jan 07 '15 at 15:18

2

Use a transaction: http://dev.mysql.com/doc/refman/5.0/en/commit.html

Also you can set up your constraint to cascade changes ON DELETES and UPDATES instead of no action. http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

answered Jan 07 '15 at 15:18

trogers1884

172
8

Never used transactions before, but first look indicates that it is exactly what I want. I don't see the point of cascading however since the transaction will rollback should any fail, no? – user1032531 Jan 07 '15 at 15:26
@user1032531 Yes transaction is all or nothing – Mihai Jan 07 '15 at 15:26
True, but if you delete a parent, you dont want orphan records in the child table. There is more than one way to add update and delete records in a database. Let the database engine do it's job. – trogers1884 Jan 07 '15 at 15:39
You can't delete a parent with no action, no? – user1032531 Jan 07 '15 at 15:41
What's to say you, or someone else goes into the table using phpMySql and deletes a parent record. (Good reason or not) Orphan records can really mess your up. That's why referential integrity is so important. Your interface is only an interface. There's nothing keeping someone from using or setting up a different interface. You also mentioned in your question that you would prefer the database to manage the integrity. – trogers1884 Jan 07 '15 at 15:44
I agree a DB solution is best, and thank you for suggesting transactions. But the DB schema will enforce preventing the deletion of a parent record with the `ON DELETE NO ACTION` constraint, no? Also, I am not saying that `CASCADE` and it seems like most DB guru's promote while many PHP people are less inclined to use. – user1032531 Jan 07 '15 at 16:00

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

of course the transaction is the best solution but if do not like to use it, you can use PDO to do multiple queries ,,, see this page

edited May 23 '17 at 12:12

Community

1
1

answered Jan 07 '15 at 15:41

Mohammad Alabed

809
6
17

Enforce inserting records into multiple tables

3 Answers3

Linked