How do I include a foreign key in the users table from a table which is created after the users table?

Question

I have two tables. Courses and the default laravel users table. I am including the course_id as a foreign key in the users table, but I am getting an error when trying to migrate since the courses table is migrated after the users table is migrated.

Error: SQLSTATE[HY000]: General error: 1005 Can't create table scholar_net.users (errno: 150 "Foreign key constraint is incorrectly formed") (Connection: mysql, SQL: alter table users add constraint users_course_id_foreign foreign key (course_id) references courses (id) on delete cascade)

Users table code:

 <?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->boolean('is_admin')->default(false);
            $table->string('user_type')->nullable();
            $table->string('dob')->nullable();
            $table->string('address')->nullable();
            $table->string('telephone_no')->nullable();
            $table->unsignedBigInteger('course_id');
            $table->foreign('course_id')->references('id')->on('courses')->onDelete('cascade');
            $table->boolean('account_status')->default(true);
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('users');
    }
};

Course table:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('courses', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            //$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->string('course_title');
            $table->string('course_description');
            $table->integer('course_price');
            $table->boolean('course_status')->default(true);
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('courses');
    }
};

Create the courses table first, or omit the foreign key in the users table on creation and add it after the Courses table has been created. — M. Eriksson, Aug 17 '23 at 08:06
Alternatively, you could try and [disable foreign key checks](https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#foreign-key-checks) during the migration. Just don't forget to enable it again. — M. Eriksson, Aug 17 '23 at 08:12
However, I would challenge adding course_id to the users table in the first place. That will limit users to only have a single course at the time. Maybe that is a requirement, but otherwise, you can create a `users_courses`-junction table, connecting user_id and course_id. Then a user can have multiple courses. If you then were to add a status column as well, you could also use it as a history of courses for each user. — M. Eriksson, Aug 17 '23 at 08:19
You could also rename the migration file of users (the date/time part) making the course table migrate first before the users. Though this is for migrating fresh since users migration file was create by default. — Mr. Kenneth, Aug 17 '23 at 08:22
**Or** you can remove the `course_id` from the users table **then** create a new migration file to insert the `course_id` to the users table. — Mr. Kenneth, Aug 17 '23 at 08:24

Mr. Kenneth · Answer 1 · 2023-08-17T08:39:43.033

There are 3 ways you can do this.

As per M Eriksson's suggestion, you can create a users_courses table for a many to many relation. But if you want to use your format where users table will have the course_id, you can do any the following:

⓵ Migrate the Course table first by changing either the filename of Course or User table (The date_time part of the filename). Change in a way where Course table is sorted first to Users table alphabetically.

ex:

2014_10_12_000000_create_users_table to 2023_08_17_999999_create_users_table

or

2023_08_17_xxxxxx_create_course_table to 2014_10_11_000000_create_course_table

Depending on what you want.

⓶ Or you can create a new migration for users table instead and insert the course_id

php artisan make:migration add_course_id_to_users_table --table=users

You then need to use the Schema::table() method (as you're accessing an existing table, not creating a new one). And you can add a column like this:

public function up()
{
    Schema::table('users', function($table) {
        $table->integer('course_id');
    });
}

and don't forget to add the rollback option:

public function down()
{
    Schema::table('users', function($table) {
        $table->dropColumn('course_id');
    });
}

Then you can run your migrations:

php artisan migrate

Orignal answer for creating migration for existing table.

⓷ Or the simplest is disabling the foreign key check in the migration file itself

public function up()
{
    Schema::disableForeignKeyConstraints();
    // drop foreign keys
    Schema::table('users', function (BluePrint $table) {
        $table->integer('course_id');
    });
    //...
    Schema::enableForeignKeyConstraints();
}

Original answer for disabling foreign key checks in migration

How do I include a foreign key in the users table from a table which is created after the users table?

1 Answers1