Laravel eloquent self realtion with FK and INDEX-CodePudding

I use mysql 8 (image: mysql:8.0). I want to create relation for user table to user table with FK and INDEX and faced with strange behaviour, why my FK did not created and how to add index o maybe when creating self relation FK and INDEX don't needed ?

my migration

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    if (!Schema::hasColumn('users', 'agent_id')) {
        Schema::table('users', function (Blueprint $table) {
            $table->integer('agent_id')->nullable(true);
            $table->foreign('agent_id')->references('id')
                ->on('users');

        });
    }
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    if (Schema::hasColumn('users', 'agent_id')) {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('agent_id');
        });
    }

}

then how to look my table repersentation in sql

create table users
(
    id                     bigint unsigned auto_increment
        primary key,
    email                  varchar(191)            not null,
    email_verified_at      timestamp               null,
    password               varchar(191)            not null,
    remember_token         varchar(100)            null,
    created_at             timestamp               null,
    updated_at             timestamp               null,
    ssn                    varchar(50)  default '' not null,
    first_name             varchar(100) default '' not null,
    last_name              varchar(100) default '' not null,
    phone                  varchar(50)  default '' not null,
    cellphone              varchar(50)  default '' not null,
    address                varchar(100) default '' not null,
    coaddress              varchar(100) default '' not null,
    zip                    varchar(50)  default '' not null,
    city                   varchar(50)  default '' not null,
    country                varchar(50)  default '' not null,
    subscribe_to_marketing tinyint(1)   default 0  not null,
    prevent_login          timestamp               null,
    last_logged_in         timestamp               null,
    deleted_at             timestamp               null,
    agent_id               int                     null,
    constraint users_email_unique
        unique (email)
);

and I try to add index like that but faced with error

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        if (!Schema::hasColumn('users', 'agent_id')) {
            Schema::table('users', function (Blueprint $table) {
                $table->integer('agent_id')->nullable(true);
                $table->foreign('agent_id')->references('id')
                    ->on('users');

            });
        }

        Schema::table('users', function (Blueprint $table) {
            if (!collect(DB::select("SHOW INDEXES FROM users"))->pluck('Key_name')->contains('users_agent_id_index')) {
                $table->index('agent_id');
            }
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        if (Schema::hasColumn('users', 'agent_id')) {
            Schema::table('users', function (Blueprint $table) {
                $table->dropColumn('agent_id');
            });
        }


        Schema::table('users', function (Blueprint $table) {
            if (collect(DB::select("SHOW INDEXES FROM users"))->pluck('Key_name')->contains('users_agent_id_index')) {
                $table->dropIndex('users_agent_id_index');
            }
        });
    }
}

error

 SQLSTATE[HY000]: General error: 3780 Referencing column 'agent_id' and referenced column 'id' in foreign key constraint 'users_agent_id_foreign' are incompatible. (SQL: alter table `users` add constraint `users_agent_id_foreign` foreign key (`agent_id`) references `users` (`id`))

CodePudding user response：

The id field on the users table is an unsigned big integer while the agent_id field you are trying to create is an integer. Change the agent_id to $table->unsignedBigInteger('agent_id')->nullable(true);

CodePudding user response：

the error says your id and agent_id columns are incompatible, because id of your users table is bigint unsigned while your agent_id is int. Change your line:

$table->integer('agent_id')->nullable(true);

By :

 $table->unsignedBigInteger('agent_id')->nullable(true);