Laravel migration incompatible foreign keys

Question

I've been told to remove the auto incrementing functionality of one of our tables, however this started to cause problems:

PDOException::("SQLSTATE[HY000]: General error: 3780 Referencing column 'limitCardId' and referenced column 'id' in foreign key constraint 'rolling_control_cards_limitcardid_foreign' are incompatible.")

Here are the keys in question:

PK $table->integer('id');

FK $table->integer('limitCardId')->unsigned()->index();

Relation $table->foreign('limitCardId')->references('id')->on('limit_cards');

I've read several other posts that suggested several solutions, however none solved my issue, here's a list of what i've already tried:

Defining this in my base table model:

    protected $primaryKey = 'id';
    public $incrementing = false;

Adding Schema::disableForeignKeyConstraints(); at the start of the up() method, and Schema::enableForeignKeyConstraints(); at the end, i've ran composer dump-autoload, php artisan config:clear, php artisan cache:clear.

As suggested, here's the stripped version of both tables, Base table:

 public function up()
    {
        Schema::create('limit_cards', function (Blueprint $table) {
            $table->integer('id');
            $table->timestamps();
        });
    }

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

Related table:

    public function up()
    {
        Schema::create('rolling_control_cards', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('limitCardId')->unsigned()->index();
            $table->unique(['controlCardNumber', 'limitCardId']);
            $table->timestamps();

            $table->foreign('limitCardId')->references('id')->on('limit_cards');
        });
    }

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

php artisan migrate:fresh --seed, Does that qualify as a fresh DB? Otherwise no, the DB has records atm. — Y.Futekov
– Y.Futekov, Commented Feb 6, 2020 at 14:44
Dropped all tables, still nothing. Thanks for the suggestion tho. — Y.Futekov
– Y.Futekov, Commented Feb 6, 2020 at 14:46
Yes, this command will drop all the tables and recreate them. Can you post the full migration of both limit_cards and rolling_control_cards tables? — mdexp
– mdexp, Commented Feb 6, 2020 at 14:46
@mdexp I'm not very confortable to post the migrations here (and it will get bloated), do you mind if post them in a chat with you? — Y.Futekov
– Y.Futekov, Commented Feb 6, 2020 at 14:49

danronmoon · Accepted Answer · 2020-02-06 15:08:23Z

9

In order for foreign key references to be compatible they must be of the exact same type, including their signedness.

$table->integer('id'); creates a signed integer column by default. Either make it unsigned to match limitCardId or make them both signed.

answered Feb 6, 2020 at 15:08

danronmoon

3,8735 gold badges36 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

W Kristianto · Accepted Answer · 2020-02-06 15:06:44Z

3

limit_cards

public function up()
{
    Schema::create('limit_cards', function (Blueprint $table) {
        $table->unsignedInteger('id');
        $table->timestamps();
    });
}

unsignedInteger => UNSIGNED INT equivalent column.

rolling_control_cards

Schema::table('rolling_control_cards', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('limitCardId')->nullable();
    $table->unique(['controlCardNumber', 'limitCardId'])
    $table->timestamps();

    $table->foreign('limitCardId')->references('id')->on('limit_cards');
});

edited Feb 6, 2020 at 15:06

answered Feb 6, 2020 at 14:58

W Kristianto

9,3737 gold badges51 silver badges73 bronze badges

3 Comments

Y.Futekov Over a year ago

What i did to solve the issue by danronmoon suggestion is - I've made both keys ->unsigned()->index();

W Kristianto Over a year ago

@Y.Futekov No problem. This will be a reference for other visitors.

Y.Futekov Over a year ago

@ Wahyu Kristianto The idea is for the PK of limit_cards to no longer be increments, just integer.

C0MM1T · Accepted Answer · 2021-01-11 16:57:05Z

0

Warning : Making "unsigned" will cause future errors if you want to switch to another RDBMS since "UNSIGNED" is for SQL only

Schema::create('rolling_control_cards', function (Blueprint $table) {
    $table->unsignedBigInteger('limitCardId');

    $table->foreign('limitCardId')->references('id')->on('limit_cards');
});

edited Jan 11, 2021 at 16:57

answered Jan 11, 2021 at 16:39

C0MM1T

11 bronze badge

Collectives™ on Stack Overflow

Laravel migration incompatible foreign keys

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related