Framework: [5.1] Migrations: set size of integer fields

[igorwanbarros]

• Laravel Version: 5.1.45 (LTS)
• PHP Version: 5.5.9
• Database Driver & Version: mysql 5.5.46

Description:

Ao criar uma migração com campo integer não tenho como definir com um determinado tamanho (11, por exemplo). Onde ao adicionar uma chave estrangeira a mesma falha.

Code example:

<?php

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

class VendasLaravelCreateVendaTable extends Migration
{
    protected $table = 'venda';


    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table($this->table, function (Blueprint $tb) {
            $tb->integer('nfe_id', false, true)->after('indicador_id');
            $tb->foreign('nfe_id')
                ->references('id')
                ->on('nota_fiscal_eletronica')
                ->onDelete('no action')
                ->onUpdate('no action');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table($this->table, function (Blueprint $tb) {
            $tb->dropForeign('venda_nfe_id_foreign');
            $tb->dropColumn('nfe_id');
        });
    }
}

Where the nota_fiscal_electronic table has a primary key with the structure

`id` UNSIGNED INT(11) NOT NULL AUTO_INCREMENT;

causing the error below:

Erro Log:

[IlluminateDatabaseQueryException]
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (#sql-427_344, CONSTRAINT venda_nfe_id_foreign FOREIGN KEY (nfe_id) REFERENCES nota_fiscal_eletronica (id) ON DELETE NO ACTION ON UPDATE NO ACTION) (SQL: alter table venda add constraint venda_nfe_id_foreign foreign key (nfe_id) references nota_fiscal_eletronica (id) on delete no action on update no action)

Because the field id has a size of 11 (INT(11)) and the foreign key field created in the venda table is INT(10), it is not possible to create the relationship.

Steps To Reproduce:

php artisan migrate

[deleugpn]

half your post is in Portuguese.

[devcircus]

the (#) following INT should not affect your relationship in anyway, It only refers to display width in the case of using zerofill. Sounds like the nota_fiscal_eletronica id column isn't available when this migration runs, or there is some other error in your migration.

[bohdan-shulha]

It's not the length issue, MySQL is not aware on this when building references on integer columns.

Try to do the following:
1) The table should be empty when you are adding non-nullable column with reference
2) Try to split column and foreign key creation to standalone Schema::table calls, like this:

        Schema::table($this->table, function (Blueprint $tb) {
            $tb->integer('nfe_id', false, true)->after('indicador_id');
        });
        // Maybe, DB::table($this->table)->update(['nfe_id' => DEFAULT_NFE_ID])
        Schema::table($this->table, function (Blueprint $tb) {
            $tb->foreign('nfe_id')
                ->references('id')
                ->on('nota_fiscal_eletronica')
                ->onDelete('no action')
                ->onUpdate('no action');
        });

Columns created during transaction are not visible to MySQL until transaction complete.

[igorwanbarros]

Actually, I was able to set up the relationship even the column being with the field INT (10). I also noticed that when using the same UNSIGNED property using 'size' the INT field is the size INT (10).

However you can add the field with the relationship with the code below:

<?php

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

class VendasLaravelCreateVendaTable extends Migration
{
    protected $table = 'venda';


    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table($this->table, function (Blueprint $tb) {
            $tb->addColumn('integer', 'nfe_id', ['unsigned' => true, 'length' => 11])->after('indicador_id')->nullable();
            $tb->index('nfe_id');
            $tb->foreign('nfe_id')
                ->references('id')
                ->on('nota_fiscal_eletronica')
                ->onDelete('no action')
                ->onUpdate('no action');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table($this->table, function (Blueprint $tb) {
            $tb->dropForeign('venda_nfe_id_foreign');
            $tb->dropColumn('nfe_id');
        });
    }
}

However it may be interesting to be able to set the size of the int field even using the UNSIGNED property (#18516 - Migrations: set size of integer fields).

Thank you for your attention!