Recursive models delete by foreign keys

You can mark your virtual foreing keys with action cascade so they will delete their referenced entities.

https://docs.phalcon.io/en/latest/reference/models.html#cascade-restrict-actions

Yes, I tried this method. The problem is that this method works only when there are no real foreign key. Any suggestion how to delete referenced models with real foreign key?

Why it only work with a real foreign key?

Dear Phalcon,

I said that it works when there are NO real foreign keys.

Without real foreign keys everything works fine, but if I add foreign keys to my table and try to delete parent record, then I get PDOException, even with action_cascade.

Why do you claim it only works when there are NO real foreign keys?

Ok, let's do the test. My SQL:

-- Server version: 5.5.38-MariaDB
-- PHP Version: 5.5.15

CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `test`;

DROP TABLE IF EXISTS `child`;
CREATE TABLE IF NOT EXISTS `child` (
`id` int(10) unsigned NOT NULL,
  `parentId` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `child` (`id`, `parentId`, `name`) VALUES
(1, 1, 'testChild');

DROP TABLE IF EXISTS `entity`;
CREATE TABLE IF NOT EXISTS `entity` (
`id` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `entity` (`id`, `name`) VALUES
(1, 'test');

ALTER TABLE `child`
 ADD PRIMARY KEY (`id`), ADD KEY `parentId` (`parentId`);

ALTER TABLE `entity`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `child`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;

ALTER TABLE `entity`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;

ALTER TABLE `child`
ADD CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parentId`) REFERENCES `entity` (`id`);

test.php

<?php

$loader = new \Phalcon\Loader();
$loader->registerDirs(array(
    './',
))->register();

$db = new \Phalcon\Db\Adapter\Pdo\Mysql(array(
    'host' => 'localhost',
    'username' => 'web',
    'password' => '',
    'dbname' => 'test'
));

$di = new \Phalcon\DI\FactoryDefault();

$di->set('db', $db);

$entity = Entity::findFirst(1);

$entity->delete();

Entity.php

<?php

use Phalcon\Mvc\Model\Relation;

class Entity extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->hasMany('id', 'Child', 'parentId', array(
            'foreignKey' => array(
                'action' => Relation::ACTION_CASCADE
            )
        ));
    }
}

Child.php

<?php

class Child extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->belongsTo('parentId', 'Entity', 'id');
    }
}

And then try to run test.php

$ php test.php 
PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parentId`) REFERENCES `entity` (`id`))' in /data/www/vhosts/bendrijos/public/test/test.php:21
Stack trace:
#0 [internal function]: PDOStatement->execute()
#1 [internal function]: Phalcon\Db\Adapter\Pdo->executePrepared(Object(PDOStatement), Array, Array)
#2 [internal function]: Phalcon\Db\Adapter\Pdo->execute('DELETE FROM `en...', Array, Array)
#3 [internal function]: Phalcon\Db\Adapter->delete('entity', '`id` = ?', Array, Array)
#4 /data/www/vhosts/bendrijos/public/test/test.php(21): Phalcon\Mvc\Model->delete()
#5 {main}
  thrown in /data/www/vhosts/bendrijos/public/test/test.php on line 21

Then we remove foreign key

ALTER TABLE child
DROP FOREIGN KEY child_ibfk_1

Restart test.php and everything works. Both entity and child records are deleted. That is why I claim it doesn't work with REAL foreign keys.

Any comment from this? I am having the same issue.

same problems here. There are two same scheme, A is innoDB and foreign keys are set, B is a MyISAM, and no foreign keys. B works good, but A say "SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails"

EDIT: I solved it, are you sure you set OK your keys at schema? I see you didnt set operation on update, delete. Cascade, set null?

When you not define operation on update, delete. Cascade, set null, by default same result with restrict delete