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

FK problem with migrations

Hi guys,

I have a problem with migrations and FKs with onDelete/onUpdate, for example generate a migration with:

DevTools 2.0.8 - last commit: c2e5aff43aa4fbf919abd13bb7d0ef80efb83f57

Tested with:

Phalcon 2.0.8 - Windows x86 for PHP 5.6.0 (vc11)

XAMPP: 5.6.14 PHP: 5.6.14 MariaDB: 10.1.8

Phalcon 2.0.8 - Windows x86 for PHP 5.5.0 (vc11)

XAMPP: 1.8.3 PHP: 5.5.15 MySQL: 5.5.32

Mysql Table

CREATE TABLE `users` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `password` CHAR(60) NOT NULL,
    `mustChangePassword` CHAR(1) NULL DEFAULT NULL,
    `idProfile` INT(10) UNSIGNED NOT NULL,
    `idCompany` INT(10) UNSIGNED NULL DEFAULT NULL,
    `banned` CHAR(1) NULL DEFAULT NULL,
    `suspended` CHAR(1) NULL DEFAULT NULL,
    `active` CHAR(1) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK_users_profiles` (`idProfile`),
    CONSTRAINT `FK_users_profiles` FOREIGN KEY (`idProfile`) REFERENCES `profiles` (`id`) ON UPDATE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Phalcon migration generates FK(references) but missing onUpdate:

<?php 

use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Db\Reference;
use Phalcon\Mvc\Model\Migration;

/**
 * Class UsersMigration_100
 */
class UsersMigration_100 extends Migration
{
    /**
     * Define the table structure
     *
     * @return void
     */
    public function morph()
    {
        $this->morphTable('users', array(
                'columns' => array(
                    new Column(
                        'id',
                        array(
                            'type' => Column::TYPE_INTEGER,
                            'unsigned' => true,
                            'notNull' => true,
                            'autoIncrement' => true,
                            'size' => 10,
                            'first' => true
                        )
                    ),
                    new Column(
                        'name',
                        array(
                            'type' => Column::TYPE_VARCHAR,
                            'notNull' => true,
                            'size' => 255,
                            'after' => 'id'
                        )
                    ),
                    new Column(
                        'email',
                        array(
                            'type' => Column::TYPE_VARCHAR,
                            'notNull' => true,
                            'size' => 255,
                            'after' => 'name'
                        )
                    ),
                    new Column(
                        'password',
                        array(
                            'type' => Column::TYPE_CHAR,
                            'notNull' => true,
                            'size' => 60,
                            'after' => 'email'
                        )
                    ),
                    new Column(
                        'mustChangePassword',
                        array(
                            'type' => Column::TYPE_CHAR,
                            'size' => 1,
                            'after' => 'password'
                        )
                    ),
                    new Column(
                        'idProfile',
                        array(
                            'type' => Column::TYPE_INTEGER,
                            'unsigned' => true,
                            'notNull' => true,
                            'size' => 10,
                            'after' => 'mustChangePassword'
                        )
                    ),
                    new Column(
                        'idCompany',
                        array(
                            'type' => Column::TYPE_INTEGER,
                            'unsigned' => true,
                            'size' => 10,
                            'after' => 'idProfile'
                        )
                    ),
                    new Column(
                        'banned',
                        array(
                            'type' => Column::TYPE_CHAR,
                            'size' => 1,
                            'after' => 'idCompany'
                        )
                    ),
                    new Column(
                        'suspended',
                        array(
                            'type' => Column::TYPE_CHAR,
                            'size' => 1,
                            'after' => 'banned'
                        )
                    ),
                    new Column(
                        'active',
                        array(
                            'type' => Column::TYPE_CHAR,
                            'size' => 1,
                            'after' => 'suspended'
                        )
                    )
                ),
                'indexes' => array(
                    new Index('PRIMARY', array('id')),
                    new Index('FK_users_profiles', array('idProfile'))
                ),
                'references' => array(
                    new Reference(
                        'FK_users_profiles',
                        array(
                            'referencedSchema' => 'sptn_web',
                            'referencedTable' => 'profiles',
                            'columns' => array('idProfile'),
                            'referencedColumns' => array('id')
                        )
                    )
                ),
                'options' => array(
                    'TABLE_TYPE' => 'BASE TABLE',
                    'AUTO_INCREMENT' => '1',
                    'ENGINE' => 'InnoDB',
                    'TABLE_COLLATION' => 'utf8_general_ci'
                ),
            )
        );
    }

    /**
     * Run the migrations
     *
     * @return void
     */
    public function up()
    {

    }

    /**
     * Reverse the migrations
     *
     * @return void
     */
    public function down()
    {

    }

}
edited Nov '15

I found the problem but i don't know the best solution.

scripts/Phalcon/Mvc/Model/Migration.php L: 323-343

GitHub

        $referencesDefinition = array();
        $references = self::$_connection->describeReferences($table, $defaultSchema);
        foreach ($references as $constraintName => $dbReference) {
            $columns = array();
            foreach ($dbReference->getColumns() as $column) {
                $columns[] = "'" . $column . "'";
            }
            $referencedColumns = array();
            foreach ($dbReference->getReferencedColumns() as $referencedColumn) {
                $referencedColumns[] = "'" . $referencedColumn . "'";
            }
            $referenceDefinition = array();
            $referenceDefinition[] = "'referencedSchema' => '" . $dbReference->getReferencedSchema() . "'";
            $referenceDefinition[] = "'referencedTable' => '" . $dbReference->getReferencedTable() . "'";
            $referenceDefinition[] = "'columns' => array(" . join(",", $columns) . ")";
            $referenceDefinition[] = "'referencedColumns' => array(".join(",", $referencedColumns) . ")";
            $referencesDefinition[] = $snippet->getReferenceDefinition($constraintName, $referenceDefinition);
        }

I tried this but not work.

$referenceDefinition[] = "'onUpdate' =>  '" . $dbReference->getOnUpdate() . "'";


6.9k

This is what you're looking for:

            'references' => array(
                new \Phalcon\Db\Reference('fk_answer_task', array(
                    'referencedTable' => 'task',
                    'columns' => array('task_id'),
                    'referencedColumns' => array('id'),
                    'onDelete' => 'CASCADE',
                    'onUpdate' => 'NO ACTION'
                )),
                new \Phalcon\Db\Reference('fk_answer_assignment', array(
                    'referencedTable' => 'assignment',
                    'columns' => array('assignment_id'),
                    'referencedColumns' => array('id'),
                    'onDelete' => 'CASCADE',
                    'onUpdate' => 'NO ACTION'
                )),         

Also see https://docs.phalcon.io/en/latest/reference/migrations.html#defining-references

'onDelete' => 'CASCADE', 'onUpdate' => 'NO ACTION'

When generate the migration not puts them in the array

This is what you're looking for:

           'references' => array(
              new \Phalcon\Db\Reference('fk_answer_task', array(
                  'referencedTable' => 'task',
                  'columns' => array('task_id'),
                  'referencedColumns' => array('id'),
                   'onDelete' => 'CASCADE',
                   'onUpdate' => 'NO ACTION'
              )),
               new \Phalcon\Db\Reference('fk_answer_assignment', array(
                   'referencedTable' => 'assignment',
                   'columns' => array('assignment_id'),
                   'referencedColumns' => array('id'),
                   'onDelete' => 'CASCADE',
                   'onUpdate' => 'NO ACTION'
               )),            

Also see https://docs.phalcon.io/en/latest/reference/migrations.html#defining-references



6.9k

I didn't get that you meant during generation of the migration files, that's a good point.

I'll look into it.



6.9k
Accepted
answer

This took a bit of searching, I fixed the problem and sent the changes upstream. As soon as they get merged you should be able to use them.

See: https://github.com/phalcon/cphalcon/pull/11162 and: https://github.com/phalcon/phalcon-devtools/pull/566

Thanks Rian!

This took a bit of searching, I fixed the problem and sent the changes upstream. As soon as they get merged you should be able to use them.

See: https://github.com/phalcon/cphalcon/pull/11162 and: https://github.com/phalcon/phalcon-devtools/pull/566