How to transform an existing column in foreign key using Laravel Migrations

Question

I'm having trouble trying to change a column type in laravel to fits it as a compatible column to be a foreign key referencing another table id fields.

I have a a schema like this:

Schema::create('person_organization', function(Blueprint $table){
   ...

   $table->integer('organization_id');
   ...
});

and I want to change the field organization_id to an unsigned type, which will make it able to be a foreign key referencing the id field in the organizations table.

NOTE: Just changing the field type in the creation of the table is not an available option, because the system is running in production mode.

So we need to make a new migration to do these changes.

NOTE 2: i tried the method change as described in laravel docs, but it sticks in a query error, as following:

Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') on delete cascade' at line 1 (SQL: alter table person_organization add constraint person_organization_person_id_foreign foreign key (person_id) references persons () on delete cascade)

Answer 1

Considering you have already installed doctrine/dbal package in your application Now create migration php artisan make:migration your_migration_name and then in migration insert the below code.

    Schema::table('persons', function(Blueprint $table) { 
    $table->integer('organization_id')->unsigned()->index()->change(); 
    $table->foreign('organization_id')->references('id')->on('organizations')- 
  >onDelete('cascade'); 
})

now run command php artisan migrate and now your are done. Happy coding...

Answer 2

From Laravel 5.6 docs:

Before modifying a column, be sure to add the doctrine/dbal dependency to your composer.json file.

composer require doctrine/dbal

Then create the migration:

php artisan make:migration add_organization_foreign_to_persons_table --table=persons

And:

Schema::table('persons', function (Blueprint $table) {
    $table->unsignedInteger('organization_id')->change();
    $table->foreign('organization_id')->references('id')->on('organizations');
});