I want to create a table with two entries referencing different users in my 'users' table. I use user_id and from_id.

When I run the migration, I get an error 'Foreign key constraint is incorrectly formed'. When I remove the two from_id lines it works. Here is my migration:

public function up()
{
    Schema::create('applicationpicture', function (Blueprint $table) {
        $table->id();

        $table->char('url')->default('');

        // When I remove the following two lines, everything works.
        $table->foreignId('from_id');
        $table->foreign('from_id')->references('id')->on('users')->onDelete('set null');

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

        $table->foreignId('event_id')->nullable();
        $table->foreign('event_id')->references('id')->on('events')->onDelete('set null');

        $table->timestamps();
    });
}

CodePudding user response：

Do it this way, there's more simple and clean way to do the same from Laravel 7.x:

public function up()
{
    Schema::create('applicationpicture', function (Blueprint $table) {
        $table->id();

        $table->char('url')->default('');


        $table->foreignId('from_id')->nullable()->constrained('users');

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

        $table->foreignId('event_id')->nullable()->constrained();

        $table->timestamps();
    });
}

The reason your migrations fail is your foreign keys are set to null onDelete but are not nullable at same time.

public function up()
{
    Schema::create('applicationpicture', function (Blueprint $table) {
        $table->id();

        $table->char('url')->default('');

        // Problem lies here just add nullable()
        $table->foreignId('from_id')->nullable();
        $table->foreign('from_id')->references('id')->on('users')->onDelete('set null');

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

        $table->foreignId('event_id')->nullable();
        $table->foreign('event_id')->references('id')->on('events')->onDelete('set null');

        $table->timestamps();
    });
}

CodePudding user response：

The simple reason is that I use onDelete('set null') for the from_id field, but I forgot to add the nullable() definition. So the correct Migration looks like this:

public function up()
{
    Schema::create('applicationpicture', function (Blueprint $table) {
        $table->id();

        $table->char('url')->default('');

        $table->foreignId('from_id')->nullable();  // Added nullable()
        $table->foreign('from_id')->references('id')->on('users')->onDelete('set null');

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

        $table->foreignId('event_id')->nullable();
        $table->foreign('event_id')->references('id')->on('events')->onDelete('set null');

        $table->timestamps();
    });
}