Hi, I'm new to this migration concept, and, using 4.3.3,
I have a "user" table, and a "task" table, both independent yet.
They already exist and have some records in it.
Now I just want to create a migration that adds a foreign key that makes "task.user_id" reference "user.id".
Here is my code:
PHP Code:
<?php
namespace App\Database\Migrations;
use CodeIgniter\Database\Migration;
class AddUserIdToTask extends Migration
{
public function up()
{
// we cannot use addForeignKey() because the table was already created
$this->forge->addColumn('task', [
'user_id' => [
'type' => 'INT',
'constraint' => 5,
'unsigned' => true,
'null' => false,
'after' => 'id'
],
]);
$sql = "ALTER TABLE task
ADD CONSTRAINT task_user_id_fk
FOREIGN KEY (user_id) REFERENCES user(id)
ON DELETE CASCADE ON UPDATE CASCADE";
$this->db->simpleQuery($sql);
}
public function down()
{
$this->forge->dropForeignKey('task', 'task_user_id_fk');
$this->forge->dropColumn('task', 'user_id');
}
}
So, the code above will work (php spark migrate) only if the "task" table is empty.
If it's not, I have a "Cannot add or update a child row: a foreign key constraint fails..." error.
Fair enough, because now I'm just playing with CodeIgniter in development, but then I wonder, what happens when my website is in production and I need to do similar changes?
So my question is: is this the regular/best practice workflow for a migration that adds a foreign key to a non-empty table?
Is my code okay?
And should i backup my data first, then empty the table, then apply the migration, then put the data back ?
Or is there something I missed ? What's the best practice workflow for adding a foreign key to an existing non-empty table?
Thanks for your advices.
Cheers, Ling.
