Welcome Guest, Not a member yet? Register   Sign In
migration and foreign keys with non empty table

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:

namespace App\Database\Migrations;


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) 

    public function down()

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.


Theme © iAndrew 2016 - Forum software by © MyBB