We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Two Tables FK on PK created in same transaction

I have two tables in my MySQL database, which were created like this:

CREATE TABLE table1 (
  id int auto_increment,
  name varchar(10),
  primary key(id)
) engine=innodb
and

CREATE TABLE table2 (
  id_fk int,
  stuff varchar(30),
  PRIMARY KEY (`id_fk`),
  CONSTRAINT id_fk FOREIGN KEY(id_fk) REFERENCES table1(id) ON DELETE CASCADE
) engine=innodb

(These are not the original tables. The point is that table2 has a foreign key referencing the primary key in table 1)

Now in my code, I would like to add entries to both of the tables within one transaction. I'm doing this:

                    $table1 = new table1();
                    $table1->setTransaction($transaction);
                    $table1->name($name);

                    if (!$table->create()) {
                        foreach ($table1->getMessages() as $message) {
                        $this->flash->error($message);
                    }
                        $transaction->rollback("Can't save table1");
                    } else {
                    $table2 = new table2();
                    $table2->setTransaction($transaction);
                    $table2->setId($table1->getId());
                    $table2->setStuff($stuff);
                    if (!$table2->create()) {
                        foreach ($table2->getMessages() as $message) {
                            $this->flash->error($message);
                        }
                        $transaction->rollback("Can't save password");
                    }
                    $transaction->commit();

The table1 create() is successful, however, when I attempt the table2 create, it always errors out with a Constraint Violation stating that the Value of field "Id" does not exist on referenced table.How can I use phalcon transaction to save data into two referenced tables in a single transaction. Thanks.



9.4k

https://dev.mysql.com/doc/refman/5.1/en/cannot-roll-back.html

13.3.2 Statements That Cannot Be Rolled Back Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.

You should design your transactions not to include such statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a ROLLBACK statement.



1.5k

Thanks, but this is not related to a rollback. If you notice, I'm trying to use an id generated when table1 has been saved in the same transaction when saving table 2 and I get a FK constraint violation when saving table 2 - since the id when saving table 1 has still not been commited in the transaction.



9.4k
Accepted
answer

HI, maybe you can use relationships between models or use raw sql instead.

See it here: https://docs.phalcon.io/en/latest/reference/models.html#storing-related-records

Hope it can help.



2.1k

HI, maybe you can use relationships between models or use raw sql instead.

See it here: https://docs.phalcon.io/en/latest/reference/models.html#storing-related-records

Hope it can help.

+1 and @navsrini, using the ORM, you don't have to deal with transaction manually for saving related records.



1.5k
edited Apr '14

Doing what is suggested above also, I get the same error message: ConstraintViolation, Value of field "Id" does not exist on referenced table. Its the same error message. Even using the above ORM, phalcon is trying to update records in both tables using a single transaction. The insert in table 2 errors out saying that it cannot find a valid reference in table 1 (since the insert in table 1 has not yet been committed). Any ideas on how this can be fixed? Thanks.



1.5k

I fixed this, made a crazy error in which I was explicity defining transactions. The suggestions above worked, thanks all. Appreciate the help. Here is the working code:

                $table1 = new table1();
                $table1->name($name);

                $table2 = new table2();
                $table2->setStuff($stuff);

                $table1->table2 = $table2;

                if (!$table->create()) {
                    foreach ($table1->getMessages() as $message) {
                    $this->flash->error($message);
                }
                } else {
                    return success;
                }