Я знаю, что было несколько постов, похожих на этот, но они не дают мне ответ, который мне нужен. Итак, я использую Phinx и пытаюсь добавить внешний ключ, но получаю следующую ошибку:
General error: 1215 Cannot add foreign key constraint
Вот файл php с внешними ключами:
<?php
use Phinx\Migration\AbstractMigration;
class CustomQuotes extends AbstractMigration
{
/**
* Change Method.
*
* Write your reversible migrations using this method.
*
* More information on writing migrations is available here:
* http://docs.phinx.org/en/latest/migrations.html#the-abstractmigration-class
*
* The following commands can be used in this method and Phinx will
* automatically reverse them when rolling back:
*
* createTable
* renameTable
* addColumn
* renameColumn
* addIndex
* addForeignKey
*
* Remember to call "create()" or "update()" and NOT "save()" when working
* with the Table class.
*/
public function change()
{
$table = $this->table('custom_quotes', ['id' => false, 'primary_key' => ['custom_quote_id']]);
$table->addColumn('custom_quote_id', 'biginteger', ['identity' =>true, 'signed' => false])
->addColumn('account_id', 'biginteger', ['limit' => 20, 'null' => true])
->addColumn('quote_type_id', 'integer', ['limit' => 11, 'null' => true])
->addColumn('quote', 'string', ['limit' => 500, 'null' => true])
->addColumn('selected', 'integer', ['limit' => 4, 'null' => true, 'default' => 1])
->addForeignKey('account_id', 'accounts', 'account_id', array('delete'=> 'NO_ACTION', 'update'=> 'NO_ACTION'))
->addForeignKey('quote_type_id', 'quote_types', 'quote_type_id', array('delete'=> 'NO_ACTION', 'update'=> 'NO_ACTION'))
->create();
}
}
Две таблицы, из которых я пытаюсь получить идентификатор, созданы перед таблицей выше. Вот две другие таблицы:
Таблица типов котировок
use Phinx\Migration\AbstractMigration;
class CustomQuotes extends AbstractMigration
{
/**
* Change Method.
*
* Write your reversible migrations using this method.
*
* More information on writing migrations is available here:
* http://docs.phinx.org/en/latest/migrations.html#the-abstractmigration-class
*
* The following commands can be used in this method and Phinx will
* automatically reverse them when rolling back:
*
* createTable
* renameTable
* addColumn
* renameColumn
* addIndex
* addForeignKey
*
* Remember to call "create()" or "update()" and NOT "save()" when working
* with the Table class.
*/
public function change()
{
$table = $this->table('custom_quotes', ['id' => false, 'primary_key' => ['custom_quote_id']]);
$table->addColumn('custom_quote_id', 'biginteger', ['identity' =>true, 'signed' => false])
->addColumn('account_id', 'biginteger', ['limit' => 20, 'null' => true])
->addColumn('quote_type_id', 'integer', ['limit' => 11, 'null' => true])
->addColumn('quote', 'string', ['limit' => 500, 'null' => true])
->addColumn('selected', 'integer', ['limit' => 4, 'null' => true, 'default' => 1])
->addForeignKey('account_id', 'accounts', 'account_id', array('delete'=> 'NO_ACTION', 'update'=> 'NO_ACTION'))
->addForeignKey('quote_type_id', 'quote_types', 'quote_type_id', array('delete'=> 'NO_ACTION', 'update'=> 'NO_ACTION'))
->create();
}
}
Таблица счетов
<?php
use Phinx\Migration\AbstractMigration;
class MakeAccountsTable extends AbstractMigration
{
/**
* Change Method.
*
* Write your reversible migrations using this method.
*
* More information on writing migrations is available here:
* http://docs.phinx.org/en/latest/migrations.html#the-abstractmigration-class
*
* The following commands can be used in this method and Phinx will
* automatically reverse them when rolling back:
*
* createTable
* renameTable
* addColumn
* renameColumn
* addIndex
* addForeignKey
*
* Remember to call "create()" or "update()" and NOT "save()" when working
* with the Table class.
*/
public function change()
{
$table = $this->table('accounts', ['id' => false, 'primary_key' => ['account_id']]);
$table->addColumn('account_id', 'biginteger', ['identity' =>true, 'signed' => false])
->addColumn('css_user_guid', 'string', ['limit' => 40])
->addColumn('state_id', 'integer', ['limit' => 11, 'null' => true])
->addColumn('phone', 'string', ['limit' => 45, 'null' => true])
->addColumn('street', 'string', ['limit' => 45, 'null' => true])
->addColumn('city', 'string', ['limit' => 45, 'null' => true])
->addColumn('zip', 'string', ['limit' => 45, 'null' => true])
->addColumn('state', 'string', ['limit' => 45, 'null' => true])
->addColumn('terms_of_service_accepted', 'boolean', ['limit' => 1, 'null' => true, 'default' => 0])
->addColumn('quote', 'string', ['limit' => 300, 'null' => true])
->addColumn('date_created', 'datetime', ['null' => true])
->create();
}
}
Так что я понял это. Оказывается, мне пришлось оставить свои account_id и quote_type_id без знака в таблице custom_quotes, потому что я сделал их без знака в таблицах account и quote_type. Это имеет смысл сейчас. Вот решение:
<?php
use Phinx\Migration\AbstractMigration;
class CustomQuotes extends AbstractMigration
{
/**
* Change Method.
*
* Write your reversible migrations using this method.
*
* More information on writing migrations is available here:
* http://docs.phinx.org/en/latest/migrations.html#the-abstractmigration-class
*
* The following commands can be used in this method and Phinx will
* automatically reverse them when rolling back:
*
* createTable
* renameTable
* addColumn
* renameColumn
* addIndex
* addForeignKey
*
* Remember to call "create()" or "update()" and NOT "save()" when working
* with the Table class.
*/
public function change()
{
$table = $this->table('custom_quotes', ['id' => false, 'primary_key' => ['custom_quote_id']]);
$table->addColumn('custom_quote_id', 'biginteger', ['identity' =>true, 'signed' => false])
->addColumn('account_id', 'biginteger', ['limit' => 20, 'null' => true, "signed" => false])
->addColumn('quote_type_id', 'integer', ['limit' => 11, 'null' => true, "signed" => false])
->addColumn('quote', 'string', ['limit' => 500, 'null' => true])
->addColumn('selected', 'integer', ['limit' => 4, 'null' => true, 'default' => 1])
->addForeignKey('account_id', 'accounts', 'account_id', array('delete'=> 'NO_ACTION', 'update'=> 'NO_ACTION'))
->addForeignKey('quote_type_id', 'quote_types', 'quote_type_id', array('delete'=> 'NO_ACTION', 'update'=> 'NO_ACTION'))
->create();
}
}
Других решений пока нет …