Add laravel migrations with foreign keys

1
votes

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();
        });
    }
    ```
2
laravelforeign-keysmigration
First of all you should use unsignedInteger for your foreign key... Expecially if you setted the departments, company and designation table ids as increment field.. Please post also the other migrations so we can help you in a more precise way... But usually when Foreign key constraint is incorrectly formed is thrown it's because the 2 columns doesn't have the same format – IlGala
@IlGala Yes I have used increment fields for departments, companies, and designations. – Gayal Seneviratne

2 Answers

1
votes

use this:

    $table->integer('department_id')->unsigned()->nullable();
    $table->integer('company_id')->unsigned()->nullable();
    $table->integer('designation_id')->unsigned()->nullable();
    $table->foreign('department_id')->references('id')->on('departments')->onDelete('cascade')->onUpdate('cascade');
    $table->foreign('company_id')->references('id')->on('departments')->onDelete('cascade')->onUpdate('cascade');
    $table->foreign('designation_id')->references('id')->on('departments')->onDelete('cascade')->onUpdate('cascade');
1
votes

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