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

Question

I'm trying to run a migration with Phalcon (Devtools 2.0.10) but it keeps complaining about ERROR: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

I've run migrations with this last week and they all worked fine, not sure what is different now. I've scrapped the database, reinstalled mysql, recreated the database, still does the same thing.

The problem seems to be related to foreign keys in general and not specifically these ones. It starts by complaining about a specific model, so I removed the references statement just to see, and it stops at the next foreign key declaration and so on. The engine is set to InnoDB, the types of the keys match and it runs fine on my colleague's machine so I'm sure it's not a syntax problem but rather something specific about SQL (server)

class ModuleTranslationsMigration_100 extends Migration
{

public function morph()
{
$this->morphTable('', array(
...
'references' => array(
    new Reference(
        'module_translations_ibfk_1',
        array(
            'referencedSchema' => 'learning',
            'referencedTable' => 'modules',
            'columns' => array('module_id'),
            'referencedColumns' => array('id')
        )
    ),
    new Reference(
        'module_translations_ibfk_2',
        array(
            'referencedSchema' => 'learning',
            'referencedTable' => 'languages',
            'columns' => array('language_id'),
            'referencedColumns' => array('id')
        )
    )
),
...

class LanguagesMigration_100 extends Migration
{

public function morph()
{
    $this->morphTable('languages', array(
            'columns' => array(
                new Column(
                    'id',
                    array(
                        'type' => Column::TYPE_CHAR,
                        'notNull' => true,
                        'size' => 2,
                        'first' => true
                    )
                ),
...

class ModulesMigration_100 extends Migration
{
    public function morph()
    {
        $this->morphTable('modules', array(
                'columns' => array(
                    new Column(
                        'id',
                        array(
                            'type' => Column::TYPE_INTEGER,
                            'unsigned' => true,
                            'notNull' => true,
                            'autoIncrement' => true,
                            'size' => 10,
                            'first' => true
                        )
                    ),
    ...

Please provide the DDL statements for the relevant tables. It's quite hard (read: impossible) to help you with so little information — Mureinik, Mar 21 '16 at 20:02
@Mureinik added one. The issue is that if I remove the references statement in this class, the same error comes up with the next model, and the next. Furthermore the migrations run on my colleague's machine, which leads me to think it's more of an sql (server) problem rather than syntax/modelling — blackbird, Mar 21 '16 at 20:06
Your problem is issued with your SQL configuration. For detailed help you should provide us your SQL shema. Also should you look into this answer here: http://stackoverflow.com/questions/15534977/mysql-cannot-add-foreign-key-constraint — yergo, Mar 23 '16 at 09:12
@yergo There's no SQL it's all DDL for Phalcon. I already checked the innodb status and it didn't provide anything useful because the keys are ok — blackbird, Mar 23 '16 at 12:25

blackbird · Accepted Answer · 2016-03-23T13:48:40.763

The problem was foreign keys at the SQL level, not a problem with syntax or the DDL per se. It seems that Phalcon will create the tables either in alphabetical file order in /migrations and doesn't take into account the table structure.

Executing

mysql> SET GLOBAL FOREIGN_KEY_CHECKS=0;

before running the migration does the trick, before setting it back of course.

I'm not sure it was necessary in this case but I removed all mysql rpms, deleted the /var/lib/mysql directory before starting over.

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

1 Answers1