[FIXED] How to change foreign key reference table name in laravel migration

Issue

I have this line in a migration table

Schema::create('xyzTableName', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('userId');
    $table->foreign('userId')->references('id')->on('userId');
    $table->timestamps();
});

where I did write on('userId') by mistake what I want is on('users')

now I did this, (in a new migration, as I can't run migrate:fresh)

 Schema::create('xyzTableName', function (Blueprint $table) {
            $table->foreign('userId')->references('id')->on('users')->change();
        });

but I don't why it is not working, please help me

Solution

You can drop the old key and build a new one

Schema::table('xyzTableName', function (Blueprint $table) {
    $table->dropForeign('xyzTableName_users_userId_foreign');
    $table->foreign('userId')->references('id')->on('users');
});

Laravel will always try to create the keys using its patterns. Since you didn't follow the name and field pattern recommended by laravel I can't be sure that string 'xyzTableName_users_userId_foreign' will be the correct one.

Check in your database the foreign key name. This is the command I'd use If MySQL

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_SCHEMA = 'db_name'
    AND REFERENCED_TABLE_NAME = 'table_name'
    AND REFERENCED_COLUMN_NAME = 'column_name';

Reference

Answered By - JMoura

Answer Checked By - Candace Johnson (PHPFixing Volunteer)