Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Saving a foreign key that does not exist throws an Exception, when using Transactions

Hello, I am trying to use transactions and detect issues when inserting. I am using Isolated Transactions on Phalcon 3.0.3 and PHP7 Here are my DB relations:

I have the following set up in the models: For Channel:

public function initialize() { $this->hasMany("ID", ChannelXPriceType::class, "pricetypeid", array('alias' => 'ChannelXPriceType')); }

For ChannelXPriceType:

public function initialize() { $this->belongsTo("price_type_id", PriceType::class, "ID", array('alias' => 'PriceType')); $this->belongsTo("channel_channel_id", Channel::class, "ID", array('alias' => 'Channel')); }

For PriceType:

public function initialize() { $this->hasMany("ID", ChannelXPriceType::class, "price_type_id", array('alias' => 'ChannelXPriceType')); }

So I think I have the relations set up correctly. Then I try Isolated Transactions like so:

$manager = new TxManager();
$transaction = $manager->get();
$channelXPriceType = new ChannelXPriceType();
$channelXPriceType->setTransaction($transaction);
$channelXPriceType->price_type_id = 99991999;
$channelXPriceType->channel_channel_id = $channel;
  if ($channelXPriceType->save() === false) {
   $transaction->rollback(
         "Cannot save channel\_x\_price\_type"
  );

}

And I get:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (busexpress_dev_kaloyan.channel_x_price_type, CONSTRAINT fk_channel_x_price_type_price_type_1 FOREIGN KEY (price_type_id) REFERENCES price_type (ID) ON DELETE NO ACTION ON UPDATE NO)

Which is an Exception

Why doesn't ->save() return false so I can handle the issue ? Also, the exception that is thrown is not Phalcon\Mvc\Model\Transaction\Failed, but a plain Exception. This makes handling the exception even harder. Does anyone have any ideas?



107.5k
Accepted
answer

You need to add virtual foreign key. Check docs for more.

Thank you. I assumed that the "virtual foreign keys" are implied, when using belongsTo. I added the foreignKey parameter and all is OK