Clash Royale CLAN TAG

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

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.

Actualy 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, wich will make it able to be a foreign key referencing the id field in the organizations table.

organization_id

id

organizations

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 stucks in a query error, as following:

change

IlluminateDatabaseQueryException : 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)


3 Answers
3

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.

php artisan make:migration your_migration_name

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...

php artisan migrate

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');
});


docrtine/dbal

$table->integer('organization_id')->unsigned();
$table->foreign('organization_id')->references('id')->on('organizations')->onDelete('cascade');


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.