Error in migration when adding a foreign key with reference option = RESTRICT

[pavlospap]

hello I am using the 2.0.8 version and I have this problem. The following code

self::$_connection->addForeignKey('table_name', self::getDbName(), new Reference('fk_table_name_01', array(
            'referencedTable' => 'ref_table',
            'columns' => array('col_name'),
            'referencedColumns' => array('id'),
            'onUpdate' => 'RESTRICT',
            'onDelete' => 'RESTRICT')
        ));

throught the phalcon migration run command will produce a foreign key with reference option = NO ACTION for both update and delete.

[sergeyklay]

Fixed in the 3.2.x branch. Feel free to open new issue if the problem appears again. Thank you for contributing.

[topotru]

It seems that removing

self::$_connection->query('SET FOREIGN_KEY_CHECKS=0'); 

crashes migrations.

For the first table created, we need a foreign key to the table that has not yet been created.

And we get an error:

 SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

Migrations, which previously worked well now fall with an error.

[sergeyklay]

// cc: @sergeysviridenko

[sergeysviridenko]

@topotru Could you provide more information and steps for reproduce?

[topotru]

@sergeysviridenko
Oh sure.
Just two tables for example.

CREATE TABLE `notes` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`text` TEXT NOT NULL,
	`created_by` INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `FK_notes_users` (`created_by`),
	CONSTRAINT `FK_notes_users` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
)
COLLATE='utf8_general_ci' ENGINE=InnoDB;

CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci' ENGINE=InnoDB;

And their migrations:

class NotesMigration_100 extends Migration
{
    public function morph()
    {
        $this->morphTable('notes', [
                'columns' => [
                    new Column(
                        'id',
                        [
                            'type' => Column::TYPE_INTEGER,
                            'unsigned' => true,
                            'notNull' => true,
                            'autoIncrement' => true,
                            'size' => 10,
                            'first' => true
                        ]
                    ),
                    new Column(
                        'text',
                        [
                            'type' => Column::TYPE_TEXT,
                            'notNull' => true,
                            'size' => 1,
                            'after' => 'id'
                        ]
                    ),
                    new Column(
                        'created_by',
                        [
                            'type' => Column::TYPE_INTEGER,
                            'unsigned' => true,
                            'notNull' => true,
                            'size' => 10,
                            'after' => 'text'
                        ]
                    )
                ],
                'indexes' => [
                    new Index('PRIMARY', ['id'], 'PRIMARY'),
                    new Index('FK_notes_users', ['created_by'], null)
                ],
                'references' => [
                    new Reference(
                        'FK_notes_users',
                        [
                            'referencedTable' => 'users',
                            'referencedSchema' => 'test',
                            'columns' => ['created_by'],
                            'referencedColumns' => ['id'],
                            'onUpdate' => 'RESTRICT',
                            'onDelete' => 'RESTRICT'
                        ]
                    )
                ],
                'options' => [
                    'TABLE_TYPE' => 'BASE TABLE',
                    'AUTO_INCREMENT' => '1',
                    'ENGINE' => 'InnoDB',
                    'TABLE_COLLATION' => 'utf8_general_ci'
                ],
            ]
        );
    }
}


class UsersMigration_100 extends Migration
{
    public function morph()
    {
        $this->morphTable('users', [
                'columns' => [
                    new Column(
                        'id',
                        [
                            'type' => Column::TYPE_INTEGER,
                            'unsigned' => true,
                            'notNull' => true,
                            'autoIncrement' => true,
                            'size' => 10,
                            'first' => true
                        ]
                    ),
                    new Column(
                        'name',
                        [
                            'type' => Column::TYPE_VARCHAR,
                            'notNull' => true,
                            'size' => 50,
                            'after' => 'id'
                        ]
                    )
                ],
                'indexes' => [
                    new Index('PRIMARY', ['id'], 'PRIMARY')
                ],
                'options' => [
                    'TABLE_TYPE' => 'BASE TABLE',
                    'AUTO_INCREMENT' => '1',
                    'ENGINE' => 'InnoDB',
                    'TABLE_COLLATION' => 'utf8_general_ci'
                ],
            ]
        );
    }
}

The reason is that migration files are processed in alphabetical order.
And the first file (NotesMigration_100) can't set the foreign key (FK_notes_users) on a non-existent table (users).

You can take code for reproduce here https://github.com/topotru/phalcon-migrations-test.git

Thank you for your contributing!

[sergeysviridenko]

@topotru This problem because referenced table isn't exist. You can add foreign key after creating second table.

[topotru]

Do you suggest creating another migration file to add foreign keys?
And what about the existing versioned migration?
I have versions from 1.0.0 to 1.9.9
Each of them contains the installation of foreign keys.
And I can't do it - I haven't versions between existing nums.
And in general it is not correct. The keys must be directly applied in the migration file for a particular table.
My backward campability is broken. And I think that not only my.

May be it's more correct to make a collection of keys when executing the migration version folder and apply it at once for all tables after completion for each version?