Yii2 - cannot add foreign key constraint

22
March 14, 2019, at 10:40 PM

I am coming from the Laravel world. I know that foreign keys are necessary for relations to work correctly. This is only my second day with the Yii2 framework.

I have set my primary indexes and foreign keys accordingly, as you can see in my code. I have two tables: A country table and a city table. A city can belong to a country, and a country has many cities:

Countries


use yii\db\Migration;
/**
 * Handles the creation of table `{{%countries}}`.
 */
class m190313_154240_create_countries_table extends Migration
{
    /**
     * {@inheritdoc}
     */
    public function safeUp()
    {
        $this->createTable('{{%countries}}', [
            'id' => $this->primaryKey(),
            'name' => $this->string(),
        ]);
        // add primary index
        $this->createIndex(
            '{{%idx-country_id}}',
            '{{%countries}}',
            'id'
        );
    }
    /**
     * {@inheritdoc}
     */
    public function safeDown()
    {
        $this->dropTable('{{%countries}}');
    }
}

Cities


use yii\db\Migration;
/**
 * Handles the creation of table `{{%cities}}`.
 * Has foreign keys to the tables:
 *
 * - `{{%country}}`
 */
class m190313_192616_create_cities_table extends Migration
{
    /**
     * {@inheritdoc}
     */
    public function safeUp()
    {
        $this->createTable('{{%cities}}', [
            'id' => $this->primaryKey(),
            'country_id' => $this->integer()->notNull(),
            'name' => $this->string('100')->notNull(),
        ]);
        // creates index for column `country_id`
        $this->createIndex(
            '{{%idx-cities-country_id}}',
            '{{%cities}}',
            'country_id'
        );
        // add foreign key for table `{{%country}}`
        $this->addForeignKey(
            '{{%fk-cities-country_id}}',
            '{{%cities}}',
            'country_id',
            '{{%country}}',
            'id',
            'CASCADE'
        );
    }
    /**
     * {@inheritdoc}
     */
    public function safeDown()
    {
        // drops foreign key for table `{{%country}}`
        $this->dropForeignKey(
            '{{%fk-cities-country_id}}',
            '{{%cities}}'
        );
        // drops index for column `country_id`
        $this->dropIndex(
            '{{%idx-cities-country_id}}',
            '{{%cities}}'
        );
        $this->dropTable('{{%cities}}');
    }
}

Yii2 does not appear to be as well documented as Laravel is. Google says I need to add primary keys. Did so, no luck. What am I missing?

Answer 1

Yii has nothing to do with your mistakes, Yii is awesome. The reason is you are providing incorrect table name in the cities migration while adding the foreign key

$this->addForeignKey(
'{{%fk-cities-country_id}}',
'{{%cities}}',
'country_id',
'{{%country}}',
'id',
'CASCADE'
);

You are creating the table with the name {{%countries}}, in the countries migration, whereas you have '{{%country}}' in the cities migration. Change it to '{{%countries}}' and it will work.

Note : you would need to drop the table cities before running the migration again.

Read More about Migrations Here

READ ALSO
PHP command in Bash does not execute code

PHP command in Bash does not execute code

Ok, so I have a problem with this thing I found on a MacBook AirIt's called terminal and you can do crazy stuff on it

57
Empty Post Data

Empty Post Data

So I'm working on a PHP projectToday wanted to start working on it again

23
why i am getting SMTP connect failed error in PHPMailer?

why i am getting SMTP connect failed error in PHPMailer?

I really don't know what's wrong in my codeI am creating contact us form by which user can send email to me

26
Use MacPorts PHP-FPM instead of from build

Use MacPorts PHP-FPM instead of from build

I've used MacPorts to install apache2, php73 and php73-fpm on MacOS MojaveI've updated by

43