Ad

Add Laravel Migrations With Foreign Keys

- 1 answer

I have created a table called 'users'.There are tables called 'companies','designations','departments'.I want to add company_id,designation_id,department_id columns to users table as foreign keys.

I tried this but it didn't work

public function up()
{
    Schema::table('users', function (Blueprint $table) {

        $table->integer('department_id');
        $table->integer('company_id');
        $table->integer('designation_id');

        $table->foreign('department_id')->references('id')->on('departments')->onDelete('restrict')->onUpdate('restrict');
        $table->foreign('company_id')->references('id')->on('companies')->onDelete('restrict')->onUpdate('restrict');
        $table->foreign('designation_id')->references('id')->on('designations')->onDelete('restrict')->onUpdate('restrict');



    });
}


public function down()
{
    Schema::table('users', function (Blueprint $table) {
        $table->dropColumn(['department_id','company_id','designation_id']);
    });
}

When I migrate the migration it shows this error.

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1005 Can't create table lanwadb.users (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table users add constraint users_department_id_foreign foreign key (department_id) references departments (id) on delete restrict on update restrict)

Designation migration as follows,

public function up()
    {
        Schema::create('designations', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestampsTz();
        });
    }

Department migration as follows,

public function up()
    {
        Schema::create('departments', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->integer('company_id');
            $table->timestampsTz();
        });
    }
    ```





Ad

Answer

To make relationship the foreign key field should be indexed. Here you have three columns you want to use as foreign key 'company_id', 'department_id' and 'designation_id'. In Laravel migration you can use unsigned() function to index them.

Example:

$table->integer('department_id')->unsigned();
$table->integer('company_id')->unsigned();
$table->integer('designation_id')->unsigned();

There is another function called unsignedInteger() by which you can make a column both Integer and Unsigned.

$table->unsignedInteger('department_id');
$table->unsignedInteger('company_id');
$table->unsignedInteger('designation_id');
Ad
source: stackoverflow.com
Ad