I create migration:

public function up() { $this->createTable('script2webpage', [ 'script_id' => $this->integer(11)->unsigned()->notNull(), 'page_id' => $this->integer(11)->unsigned()->notNull(), ]); $this->addForeignKey('FK_ITEM_SCRIPT','script2webpage', 'script_id','scripts','id'); $this->addForeignKey('FK_ITEM_WEBPAGE','script2webpage', 'page_id','webpage','page_id'); } public function down() { $this->dropForeignKey('FK_ITEM_SCRIPT', 'script2webpage'); $this->dropForeignKey('FK_ITEM_WEBPAGE', 'script2webpage'); $this->dropTable('script2webpage'); }

Migration error

 Error Info: Array ( [0] => HY000 [1] => 1005 [2] => Can't create table 'ms.#sql-1ed4_2e' (errno: 150) )

The first FK is created. On 2 error:

  add foreign key FK_ITEM_WEBPAGE: script2webpage (page_id) references webpage (page_id) ...Exception 'yii\db\Exception' with message 'SQLSTATE[HY000]: General error: 1005 Can't create table 'tablename.#sql-1ed4_2e' (errno: 150) The SQL being executed was: ALTER TABLE `script2webpage` ADD CONSTRAINT `FK_ITEM_WEBPAGE` FOREIGN KEY (`page_id`) REFERENCES `webpage` (`page_id`)'

Perhaps this is due to the table engines:

 CREATE TABLE ms.scripts ( id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, title varchar(50) NOT NULL, code text DEFAULT NULL, url varchar(255) DEFAULT NULL, for_all tinyint(1) DEFAULT NULL, status tinyint(1) DEFAULT NULL, description text DEFAULT NULL, created_at datetime DEFAULT NULL, updated_at datetime DEFAULT NULL, PRIMARY KEY (id) ) ENGINE = INNODB, CHARACTER SET utf8, COLLATE utf8_general_ci; CREATE TABLE ms.webpage ( page_id int(11) UNSIGNED NOT NULL AUTO_INCREMENT, title varchar(255) DEFAULT NULL, url varchar(128) NOT NULL, body text DEFAULT NULL, layout varchar(72) DEFAULT 'main', menu_title varchar(255) DEFAULT NULL, seo_title varchar(255) DEFAULT NULL, seo_description text DEFAULT NULL, seo_keywords text DEFAULT NULL, status tinyint(1) UNSIGNED NOT NULL DEFAULT 1, PRIMARY KEY (page_id) ) ENGINE = MYISAM, AUTO_INCREMENT = 15, AVG_ROW_LENGTH = 1806, CHARACTER SET utf8, CHECKSUM = 0, COLLATE utf8_general_ci; ALTER TABLE ms.webpage ADD UNIQUE INDEX url (url);

How in this case to get rid of the error?

    1 answer 1

    Good morning.

    The causes of the error "150" may be several.

    First make both InnoDB tables.

    Then index those fields in the parent table that the child table will refer to. And only after that assign foreign keys.

    For example:

      $this->createIndex('idx-product_id', '{{%products_categories}}', 'product_id'); $this->addForeignKey('fk-prod-cat-product_id', '{{%products_categories}}', 'product_id', '{{%products}}', 'id', 'CASCADE', 'RESTRICT');

    Sometimes you want to turn off the verification of foreign keys. This can be done like this:

     $this->db->createCommand('SET foreign_key_checks=0')->execute(); // назначаете индексы, внешние ключи или выполняете что-то ещё. $this->db->createCommand('SET foreign_key_checks=1')->execute();

    But it is better to initially plan the creation of tables in order not to do this.

    Regarding errors, the first available link . Perhaps you will help.