Adding a Foreign Key Using Phinx

Question

I'm trying to add foreign keys to a table using a Phinx migration. My intention is to create a table ('sales_order_attachment') and add two foreign keys. There's not too much Phinx documentation, so I haven't found an answer on the internet or through tinkering (yet). What am I doing wrong here? Both tables and keys the migration is pointing to exist.

Migration:

public function up()
{
    $table = $this->table('sales_order_attachment', array('id' => 'sales_order_attachment_id'));
    $table->addColumn('file_upload_id', 'integer')
        ->addForeignKey('file_upload_id', 'file_upload', 'id');
    $table->addColumn('sales_order_id', 'integer')
        ->addForeignKey('sales_order_id', 'sales_order', 'id')
        ->save();
}

public function down()
{
    $this->table('sales_order_attachment')->drop();
}

Error:

[PDOException]                                                                                            
SQLSTATE[HY000]: General error: 1005 Can't create table 'table.sales_order_attachment' (errno: 150)  

Answer 1

Do your other two tables already exist?

• file_upload
• sales_order

Maybe you could also try creating the 'sales_order_attachment' table first then adding the foreign keys in a separate step.

Regards,

Rob

Answer 2

I use phinx, but I found the ORM wrapping to be useless. The reason is that ORMs tend to be very difficult to debug.

Try this instead, (assuming this is MySQL, run it in whatever database you need):

public function up()
    {
        $exists = $this->hasTable('sales_order_attachment');
        if (!$exists) {
            $this->execute("CREATE TABLE `sales_order_attachment` (
                          `sales_order_attachment_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                          `minimum_quantity` int(10) unsigned NOT NULL DEFAULT '0',
                          PRIMARY KEY (`sales_order_attachment_id`),
                          CONSTRAINT `sales_order_id` FOREIGN KEY (`sales_order_id`) REFERENCES `sales_order` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
        }
    }
    public function down()
    {
        $exists = $this->hasTable('sales_order_attachment');
        if ($exists) {
            $table = $this->table('sales_order_attachment');
            $table->drop();
        }
    }

I've found this to be the easiest to debug and use. The only gotcha is if you are using multiple different databases, you'll need to update your phinx.yml file to point to a different scripts folder, e.g. %%PHINX_CONFIG_DIR%%/sqlite_scripts before running it on sqlite databases or whatever else you are using.

Answer 3

I had the same problem and it was being caused by not setting the 'delete' and 'update' options on the foreign keys properly. Try:

public function up()
{
    $table = $this->table('sales_order_attachment', array('id' => 'sales_order_attachment_id'));
    $table->addColumn('file_upload_id', 'integer', array('limit'=>10))
        ->addForeignKey('file_upload_id', 'file_upload', 'id', array('delete'=>'RESTRICT', 'update'=>'CASCADE'));
    $table->addColumn('sales_order_id', 'integer', array('limit'=>10))
        ->addForeignKey('sales_order_id', 'sales_order', 'id', array('delete'=>'RESTRICT', 'update'=>'CASCADE'))
        ->save();
}

I am using Phinx 0.5.3 & slim 3.0