laravel migration best way to add foreign key

Question

Simple question: I'm new to Laravel. I have this migration file:

Schema::create('lists', function(Blueprint $table) {
    $table->increments('id'); 
    $table->string('title', 255);
    $table->integer('user_id')->unsigned(); 
    $table->foreign('user_id')->references('id')->on('users'); 
    $table->timestamps();
});

I want to update it to add onDelete('cascade').

What's the best way to do this?

Answer 1

Firstly you have to make your user_id field an index:

$table->index('user_id');

After that you can create a foreign key with an action on cascade:

$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

If you want to do that with a new migration, you have to remove the index and foreign key firstly and do everything from scratch.

On down() function you have to do this and then on up() do what I've wrote above:

$table->dropForeign('lists_user_id_foreign');
$table->dropIndex('lists_user_id_index');
$table->dropColumn('user_id');

Answer 2

In Laravel 7 it can be done in one line

$table->foreignId('user_id')->constrained()->cascadeOnDelete();

Answer 3

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

    $table->bigIncrements('id');
    $table->string('name');
    $table->timestamps();

});

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

    $table->unsignedBigInteger('role_id');
    $table->foreign('role_id')->references('id')->on('roles');
    $table->string('permission');

});

Answer 4

$table->integer('user_id')->unsigned();
$table->foreign('user_id')->references('id')->on('users');

In this example, we are stating that the user_id column references the id column on the users table. Make sure to create the foreign key column first! The user_id column is declared unsigned because it cannot have negative value.

You may also specify options for the "on delete" and "on update" actions of the constraint:

$table->foreign('user_id')
      ->references('id')->on('users')
      ->onDelete('cascade');

To drop a foreign key, you may use the dropForeign method. A similar naming convention is used for foreign keys as is used for other indexes:

$table->dropForeign('posts_user_id_foreign');

If you are fairly new to Laravel and Eloquent, try out the Laravel From Scratch series available on laracasts. It is a great guide for beginners.

Answer 5

let's say you have two tables student and section , you can refer the following two table structure for adding foreign key and making onDelete('cascade') .

Table -1 :

public function up()
{
    Schema::create('student', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('address');
        $table->string('phone');
        $table->string('about')->nullable();
        $table->timestamps();
    });
}

Table - 2:

public function up()
{
    Schema::create('section', function (Blueprint $table) {
        $table->id();
        $table->bigInteger('student_id')->unsigned()->index()->nullable();
        $table->foreign('student_id')->references('id')->on('student')->onDelete('cascade');
        $table->string('section')->nullable();
        $table->string('stream')->nulable();
        $table->timestamps();
    });
}

hope it will help you -:) you can read the full article from here .

Answer 6

Laravel 7.x Foreign Key Constraints

Laravel also provides support for creating foreign key constraints, which are used to force referential integrity at the database level. For example, let's define a user_id column on the posts table that references the id column on a users table:

Schema::table('posts', function (Blueprint $table) {
    $table->unsignedBigInteger('user_id');

    $table->foreign('user_id')->references('id')->on('users');
});

Since this syntax is rather verbose, Laravel provides additional, terser methods that use convention to provide a better developer experience. The example above could be written like so:

Schema::table('posts', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained();
});

Source: https://laravel.com/docs/7.x/migrations

Answer 7

As of Laravel 8:

$table->foreignIdFor(OtherClass::class)->constrained();

So simple :)

• Make sure that the OtherClass migration file is running EARLIER (by filename date as usual).
• If the OtherClass id is not autoincrementing, the otherclass_id would have a type of char instead of bigint, in which case->

Use this instead:

$table->foreignId('otherclass_id')->index()->constrained()->cascadeOnDelete();

Answer 8

You should create a new migration file let's say 'add_user_foreign_key.php'

public function up()
{
    Schema::table('lists', function(Blueprint $table)
    {
         $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('lists', function(Blueprint $table)
    {
    $table->dropForeign('user_id'); //
    });
}  

The run

 php artisan migrate

Answer 9

Schema::table('posts', function (Blueprint $table) {
    $table->unsignedInteger('user_id');

    $table->foreign('user_id')->references('id')->on('users');
});

Answer 10

If you want to add onDelete('cascade') on the existing foreign key, just drop the indexes and create them again:

public function up()
{
    Schema::table('lists', function($table)
    {
        $table->dropForeign('lists_user_id_foreign');

        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    });
}

public function down()
{
    Schema::table('lists', function($table)
    {
        $table->dropForeign('lists_user_id_foreign');

        $table->foreign('user_id')->references('id')->on('users');
    });
}

Answer 11

I was doing the same but got error " id not exist" => so I changed my migration file as below :

question table migration content:

$table->id() => should change to $table->increments('id')

definitions of foreign key in Reply table:

$table->foreign('question_id')->references('id')->on('questions')->onDelete('cascade');

now your foreign key will work.

Answer 12

for versions before 7x;

Schema::create('lists', function(Blueprint $table) {
    $table->increments('id'); 
    $table->string('title', 255);
    $table->unsignedBigInteger('user_id')->index(); 
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade'); 
    $table->timestamps();
});

for version 7+;

Since this syntax is rather verbose, Laravel provides additional, terser methods that use conventions to provide a better developer experience. When using the foreignId method to create your column, the example above can be rewritten like so:

Schema::create('lists', function(Blueprint $table) {
    $table->increments('id'); 
    $table->string('title', 255);
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
    $table->timestamps();
});

The foreignId method is an alias for unsignedBigInteger while the constrained method will use convention to determine the table and column name being referenced. If your table name does not match the convention, you may specify the table name by passing it as an argument to the constrained method:

Schema::create('lists', function(Blueprint $table) {
    $table->foreignId('user_id')->constrained('users')->onDelete('cascade');
});

source: https://laravel.com/docs/7.x/migrations#foreign-key-constraints

Answer 13

Clear, modern and Straightforward approach

suppose parent: `Book Model` and `books table`
suppose child : `Page Model` and `pages table`

$table->foreignId('book_id')->references('id')->on('books');

where book_id is is the colomn name in child (pages table)
and id is the linkage between the Parent and Child tables, books and pages tables,
and books is the table name to which we are going to link