Foreign key constraint fails on existing key

- 1 answer

Ad

In an existing Laravel project we encounter a vague problem when inserting a new record in our mysql database. The workflow is like this, whenever we create a new company, we also create users which belong to that company, somehow when submitting the new user, the foreign key constraint fails while we are 100% certain that key exists, and that we have the right key in hands.

Even stranger is that sometimes the creation goes well,and we can insert many users after that without any problems.

I hope that someone can point me in the right direction on how to proceed or debug this further.

EDIT: I've added some code, i don't think there is anything special here, except for the nullable on the foreignkey. This is a relative new addition, because not every user belongs to a company, so that's why I added that one.

I think the question would be: how can a foreign key fail, when you know (verify) that this key exists, is the right key and is 100% sure of the right type.

    $company = new Company();
    $company->fill($request->all());
    $company->showLicence = (int)$request->get('showLicence');

    $company->save();

    //create a zero-user for each company to be able to login support    staff
    $user = new User();

    $user->name = "user";

    $user->password = bcrypt('password');
    $user->company_id = $company->id;
    $user->save();

//user migration
Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('email')->unique();
        $table->string('password', 60);
        $table->rememberToken();
        $table->timestamps();
        $table->string('profession');
        $table->integer('productivity');
        $table->date('birthDay');
        $table->boolean('active');
        $table->string('standardCalendar');
        $table->string('calendar');
        $table->integer('department')->unsigned();

        $table->integer('company_id')->unsigned()->nullable();

        $table->foreign('company_id')
            ->references('id')
            ->on('users')
            ->onDelete('cascade');
    });

    Schema::create('companies', function (Blueprint $table) {
        $table->increments('id');
        $table->timestamps();
        $table->string('companyName');
        $table->string('street');
        $table->string('zipcode');
        $table->string('city');
        $table->string('phone');
        $table->string('fax');
        $table->string('email');
        $table->string('website');
        $table->string('logo');
        $table->string('taxNumber');
        $table->string('welcome');
        $table->boolean('showLicence');
    });
Ad

Answer

Ad

In your migration code, the foreign key company_id on users actually refers to itself, not the company table. Change ->on('users') into ->on('companies').

As an additional tip, if your Company Eloquent model has a users relation (a method returning $this->hasMany('User');), you can insert the new user as follows:

$user = $company->users()->create([
    'name' => 'user',
    'password' => bcrypt('password'),
]);
Ad
source: stackoverflow.com
Ad