CODEWITHSUNDEEP
mysqllaraveleloquent
Y.Futekov
Y.Futekov

Reputation: 544

Laravel migration incompatible foreign keys

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');
    }

Upvotes: 5

Views: 6107

Answers (3)

C0MM1T
C0MM1T

Reputation: 1

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');
});

Upvotes: 0

danronmoon
danronmoon

Reputation: 3873

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.

Upvotes: 9

W Kristianto
W Kristianto

Reputation: 9303

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');
});

Upvotes: 3

Related Questions