Dropping foreign keys

Published 10 months ago by afonte15

I have 5 tables and I am trying to drop foreign keys properly but I am unable to.

create_employees_table


<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateEmployeesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('employees', function (Blueprint $table) {
            $table->increments('id');
            $table->string('lastName', 25);
            $table->string('firstName', 25);
            $table->string('department', 35);
            $table->timestamps();
            //
        });
    }
    
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        
        Schema::drop('employees');
    }
}

ceate_locations_table


<?php

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

class CreateLocationsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('locations', function (Blueprint $table) {
            $table->increments('locationId');
            $table->string('building', 25);
            $table->string('room',25);

        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('locations');
    }
}

create_devices_table


<?php

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

class CreateDevicesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('devices', function(Blueprint $table) {
            $table->increments('deviceId');
            $table->string('manufacturer', 25);
            $table->string('device', 25);
            $table->string('model', 25);
            $table->string('tag', 25);
            $table->string('macAddress',17);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('devices');
    }
}

create_status_table

<?php

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

class CreateStatusesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('statuses', function (Blueprint $table) {
            $table->string('status', 20);
            $table->text('note', 300);
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('statuses');
    }
}

create_inventories_table

<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateInventoriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('inventories', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('employee_id')->unsigned();
            $table->integer('location_id')->unsigned();
            $table->integer('device_id')->unsigned();
            $table->foreign('employee_id')->references('id')->on('employees')->onDelete('cascade');
            $table->foreign('location_id')->references('id')->on('locations')->onDelete('cascade');
            $table->foreign('device_id')->references('id')->on('devices')->onDelete('cascade');
            $table->timestamps();
        });
    }
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('inventories');
    }
}
    

I tried dropping the foreign keys from the inventories with the code below and tried similar code on the employees table. Whatever I try is failing so I know it is my mistake but not sure how to do this correctly. Any help would be much appreciated.

public function down()

 {
     Schema::table('inventories', function (Blueprint $table) {
           $table->dropForeign('employee_id_foreign');
         $table->dropColumn('employee_id');
      });

      Schema::drop('inventories');
    }
}

Dunsti

the names of the foreign-keys are named like this:

[own-tablename]_[own-fieldname]_foreign

maybe you should try

$table->dropForeign('inventories_employee_id_foreign');

Otherwise you can look directly in MySQL for the generated name:

show create table inventories;

afonte15

I wrote it how you suggested and I doubled check mysql generated name and that is the correct generated name for the foreign key but I still get the same error.

create_inventories_table

 public function down()
    
    {
        Schema::table('inventories', function (Blueprint $table) {
            $table->dropForeign('inventories_employee_id_foreign');
            $table->dropColumn('employee_id');
        });

        Schema::drop('inventories');

    }
}

I even tried. But it still fails....

create_employees_table


public function down()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table->dropForeign('inventories_employee_id_foreign');
           $table->dropColumn('inventories_employee_id');
        });

                Schema::drop('employees');
}
}
w1n78

@afonte15 make sure you are using dbal

Before modifying a column, be sure to add the doctrine/dbal dependency to your composer.json file. The Doctrine DBAL library is used to determine the current state of the column and create the SQL queries needed to make the specified adjustments to the column:

composer require doctrine/dbal

https://laravel.com/docs/5.5/migrations

afonte15

It is laravel 5.2 but I will look into that. Thank you.

sutherland

If you pass dropForeign an array with the column name it should automatically drop the foreign key according to the default naming convention:

$table->dropForeign(['employee_id']);
$table->dropColumn('employee_id');
afonte15

I tired it 2 ways and it still fails.

Both migrations listed below is the inventories_table.

<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateInventoriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('inventories', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('employee_id')->unsigned();
            $table->integer('location_id')->unsigned();
            $table->integer('device_id')->unsigned();
            $table->foreign('employee_id')->references('id')->on('employees')->onDelete('cascade');
            $table->foreign('location_id')->references('id')->on('locations')->onDelete('cascade');
            $table->foreign('device_id')->references('id')->on('devices')->onDelete('cascade');
            $table->timestamps();
        });
    }
    
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    
    public function down()
    
    {
    
        Schema::table('inventories', function(Blueprint $table)
        {
            $table->dropForeign(['employee_id_foreign']);
            $table->dropColumn('employee_id');
            $table->dropForeign(['location_id']);
            $table->dropColumn('location_id');
            $table->dropForeign(['device_id']);
            $table->dropColumn('device_id');
    
    
    
        });
            Schema::drop('inventories');
    }
}



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

class CreateInventoriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('inventories', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('employee_id')->unsigned();
            $table->integer('location_id')->unsigned();
            $table->integer('device_id')->unsigned();
            $table->foreign('employee_id')->references('id')->on('employees')->onDelete('cascade');
            $table->foreign('location_id')->references('id')->on('locations')->onDelete('cascade');
            $table->foreign('device_id')->references('id')->on('devices')->onDelete('cascade');
            $table->timestamps();
        });
    }
    
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    
    public function down()
    
    {
    
        Schema::table('inventories', function(Blueprint $table)
        {
            $table->dropForeign(['inventories_employee_id_foreign']);
            $table->dropColumn('employee_id');
            $table->dropForeign(['inventories_location_id']);
            $table->dropColumn('inventories_location_id');
            $table->dropForeign(['inventories_device_id']);
            $table->dropColumn('device_id');
    
    
    
        });
            Schema::drop('inventories');
    }
}

The exact Errors I recieve is listed below.

Error 1/2


PDOException in Connection.php line 457:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`lawInventory`.`inventories`, CONSTRAINT `inventories_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE)
in Connection.php line 457
at PDOStatement->execute(array('2017-10-11 19:33:40', '2017-10-11 19:33:40')) in Connection.php line 457
at Connection->Illuminate\Database\{closure}(object(MySqlConnection), 'insert into `inventories` (`updated_at`, `created_at`) values (?, ?)', array('2017-10-11 19:33:40', '2017-10-11 19:33:40')) in Connection.php line 722
at Connection->runQueryCallback('insert into `inventories` (`updated_at`, `created_at`) values (?, ?)', array('2017-10-11 19:33:40', '2017-10-11 19:33:40'), object(Closure)) in Connection.php line 685
at Connection->run('insert into `inventories` (`updated_at`, `created_at`) values (?, ?)', array('2017-10-11 19:33:40', '2017-10-11 19:33:40'), object(Closure)) in Connection.php line 458
at Connection->statement('insert into `inventories` (`updated_at`, `created_at`) values (?, ?)', array('2017-10-11 19:33:40', '2017-10-11 19:33:40')) in Connection.php line 414
at Connection->insert('insert into `inventories` (`updated_at`, `created_at`) values (?, ?)', array('2017-10-11 19:33:40', '2017-10-11 19:33:40')) in Processor.php line 32
at Processor->processInsertGetId(object(Builder), 'insert into `inventories` (`updated_at`, `created_at`) values (?, ?)', array('2017-10-11 19:33:40', '2017-10-11 19:33:40'), 'id') in Builder.php line 2107
at Builder->insertGetId(array('2017-10-11 19:33:40', '2017-10-11 19:33:40'), 'id')
at call_user_func_array(array(object(Builder), 'insertGetId'), array(array('updated_at' => '2017-10-11 19:33:40', 'created_at' => '2017-10-11 19:33:40'), 'id')) in Builder.php line 1423
at Builder->__call('insertGetId', array(array('updated_at' => '2017-10-11 19:33:40', 'created_at' => '2017-10-11 19:33:40'), 'id')) in Model.php line 1628
at Model->insertAndSetId(object(Builder), array('updated_at' => '2017-10-11 19:33:40', 'created_at' => '2017-10-11 19:33:40')) in Model.php line 1597
at Model->performInsert(object(Builder), array()) in Model.php line 1488
at Model->save() in Model.php line 574
at Model::create(array('_token' => 'rDoLcbyfisd8UJWBySWtuODGbdv4zYRun4wRjNj0', 'lastName' => 'Fon', 'firstName' => 'Ann', 'department' => 'Helpdesk', 'building' => 'VH', 'room' => '211', 'manufacturer' => 'Dell', 'device' => 'desktop', 'model' => 'Optiplex 9010', 'tag' => '123456', 'macAddress' => '1q2w3e4r5t6y', 'status' => 'active', 'notes' => 'No Notes Needed')) in InventoriesController.php line 51
at InventoriesController->store(object(InventoryRequest))
at call_user_func_array(array(object(InventoriesController), 'store'), array(object(InventoryRequest))) in Controller.php line 80
at Controller->callAction('store', array(object(InventoryRequest))) in ControllerDispatcher.php line 146
at ControllerDispatcher->call(object(InventoriesController), object(Route), 'store') in ControllerDispatcher.php line 94
at ControllerDispatcher->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102
at Pipeline->then(object(Closure)) in ControllerDispatcher.php line 96
at ControllerDispatcher->callWithinStack(object(InventoriesController), object(Route), object(Request), 'store') in ControllerDispatcher.php line 54
at ControllerDispatcher->dispatch(object(Route), object(Request), 'App\Http\Controllers\InventoriesController', 'store') in Route.php line 174
at Route->runController(object(Request)) in Route.php line 140
at Route->run(object(Request)) in Router.php line 724
at Router->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in VerifyCsrfToken.php line 64
at VerifyCsrfToken->handle(object(Request), object(Closure))
at call_user_func_array(array(object(VerifyCsrfToken), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in ShareErrorsFromSession.php line 49
at ShareErrorsFromSession->handle(object(Request), object(Closure))
at call_user_func_array(array(object(ShareErrorsFromSession), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in StartSession.php line 64
at StartSession->handle(object(Request), object(Closure))
at call_user_func_array(array(object(StartSession), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in AddQueuedCookiesToResponse.php line 37
at AddQueuedCookiesToResponse->handle(object(Request), object(Closure))
at call_user_func_array(array(object(AddQueuedCookiesToResponse), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in EncryptCookies.php line 59
at EncryptCookies->handle(object(Request), object(Closure))
at call_user_func_array(array(object(EncryptCookies), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102
at Pipeline->then(object(Closure)) in Router.php line 726
at Router->runRouteWithinStack(object(Route), object(Request)) in Router.php line 699
at Router->dispatchToRoute(object(Request)) in Router.php line 675
at Router->dispatch(object(Request)) in Kernel.php line 246
at Kernel->Illuminate\Foundation\Http\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in CheckForMaintenanceMode.php line 44
at CheckForMaintenanceMode->handle(object(Request), object(Closure))
at call_user_func_array(array(object(CheckForMaintenanceMode), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102
at Pipeline->then(object(Closure)) in Kernel.php line 132
at Kernel->sendRequestThroughRouter(object(Request)) in Kernel.php line 99
at Kernel->handle(object(Request)) in index.php line 53

Error 2/2



QueryException in Connection.php line 729:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`lawInventory`.`inventories`, CONSTRAINT `inventories_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE) (SQL: insert into `inventories` (`updated_at`, `created_at`) values (2017-10-11 19:33:40, 2017-10-11 19:33:40))
in Connection.php line 729
at Connection->runQueryCallback('insert into `inventories` (`updated_at`, `created_at`) values (?, ?)', array('2017-10-11 19:33:40', '2017-10-11 19:33:40'), object(Closure)) in Connection.php line 685
at Connection->run('insert into `inventories` (`updated_at`, `created_at`) values (?, ?)', array('2017-10-11 19:33:40', '2017-10-11 19:33:40'), object(Closure)) in Connection.php line 458
at Connection->statement('insert into `inventories` (`updated_at`, `created_at`) values (?, ?)', array('2017-10-11 19:33:40', '2017-10-11 19:33:40')) in Connection.php line 414
at Connection->insert('insert into `inventories` (`updated_at`, `created_at`) values (?, ?)', array('2017-10-11 19:33:40', '2017-10-11 19:33:40')) in Processor.php line 32
at Processor->processInsertGetId(object(Builder), 'insert into `inventories` (`updated_at`, `created_at`) values (?, ?)', array('2017-10-11 19:33:40', '2017-10-11 19:33:40'), 'id') in Builder.php line 2107
at Builder->insertGetId(array('2017-10-11 19:33:40', '2017-10-11 19:33:40'), 'id')
at call_user_func_array(array(object(Builder), 'insertGetId'), array(array('updated_at' => '2017-10-11 19:33:40', 'created_at' => '2017-10-11 19:33:40'), 'id')) in Builder.php line 1423
at Builder->__call('insertGetId', array(array('updated_at' => '2017-10-11 19:33:40', 'created_at' => '2017-10-11 19:33:40'), 'id')) in Model.php line 1628
at Model->insertAndSetId(object(Builder), array('updated_at' => '2017-10-11 19:33:40', 'created_at' => '2017-10-11 19:33:40')) in Model.php line 1597
at Model->performInsert(object(Builder), array()) in Model.php line 1488
at Model->save() in Model.php line 574
at Model::create(array('_token' => 'rDoLcbyfisd8UJWBySWtuODGbdv4zYRun4wRjNj0', 'lastName' => 'Fon', 'firstName' => 'Ann', 'department' => 'Helpdesk', 'building' => 'VH', 'room' => '211', 'manufacturer' => 'Dell', 'device' => 'desktop', 'model' => 'Optiplex 9010', 'tag' => '123456', 'macAddress' => '1q2w3e4r5t6y', 'status' => 'active', 'notes' => 'No Notes Needed')) in InventoriesController.php line 51
at InventoriesController->store(object(InventoryRequest))
at call_user_func_array(array(object(InventoriesController), 'store'), array(object(InventoryRequest))) in Controller.php line 80
at Controller->callAction('store', array(object(InventoryRequest))) in ControllerDispatcher.php line 146
at ControllerDispatcher->call(object(InventoriesController), object(Route), 'store') in ControllerDispatcher.php line 94
at ControllerDispatcher->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102
at Pipeline->then(object(Closure)) in ControllerDispatcher.php line 96
at ControllerDispatcher->callWithinStack(object(InventoriesController), object(Route), object(Request), 'store') in ControllerDispatcher.php line 54
at ControllerDispatcher->dispatch(object(Route), object(Request), 'App\Http\Controllers\InventoriesController', 'store') in Route.php line 174
at Route->runController(object(Request)) in Route.php line 140
at Route->run(object(Request)) in Router.php line 724
at Router->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in VerifyCsrfToken.php line 64
at VerifyCsrfToken->handle(object(Request), object(Closure))
at call_user_func_array(array(object(VerifyCsrfToken), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in ShareErrorsFromSession.php line 49
at ShareErrorsFromSession->handle(object(Request), object(Closure))
at call_user_func_array(array(object(ShareErrorsFromSession), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in StartSession.php line 64
at StartSession->handle(object(Request), object(Closure))
at call_user_func_array(array(object(StartSession), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in AddQueuedCookiesToResponse.php line 37
at AddQueuedCookiesToResponse->handle(object(Request), object(Closure))
at call_user_func_array(array(object(AddQueuedCookiesToResponse), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in EncryptCookies.php line 59
at EncryptCookies->handle(object(Request), object(Closure))
at call_user_func_array(array(object(EncryptCookies), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102
at Pipeline->then(object(Closure)) in Router.php line 726
at Router->runRouteWithinStack(object(Route), object(Request)) in Router.php line 699
at Router->dispatchToRoute(object(Request)) in Router.php line 675
at Router->dispatch(object(Request)) in Kernel.php line 246
at Kernel->Illuminate\Foundation\Http\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in CheckForMaintenanceMode.php line 44
at CheckForMaintenanceMode->handle(object(Request), object(Closure))
at call_user_func_array(array(object(CheckForMaintenanceMode), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102
at Pipeline->then(object(Closure)) in Kernel.php line 132
at Kernel->sendRequestThroughRouter(object(Request)) in Kernel.php line 99
at Kernel->handle(object(Request)) in index.php line 53

Devon

@afonte15, the issue is because you're not sticking to a naming convention... On some fields, you're using lowerCamelCase while on others you're using snake_case... Laravel, by default, prefers that you use snake_case - though you can override it explicitly if necessary.

Furthermore, the autoinc columns are assumed to be named id and not tableId... That said, change locationId to id on the locations table and deviceId to id on the devices table and it should work.

afonte15

@Devon I actually did make those changes and when I did I was able to drop and add (php artisan migrate:reset and php artisan migrate) the tables without issues but I still get the error above regarding the foreign keys. Below is the current and updated migration codes and still receiving the error above.

employees table

<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateEmployeesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('employees', function (Blueprint $table) {
            $table->increments('id');
            $table->string('lastName', 25);
            $table->string('firstName', 25);
            $table->string('department', 35);
            $table->timestamps();
            //
        });
    }
    
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('employees');
}
}

locations table

<?php

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

class CreateLocationsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('locations', function (Blueprint $table) {
            $table->increments('id');
            $table->string('building', 25);
            $table->string('room',25);

        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('locations');
    }
}


devices table


<?php

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

class CreateDevicesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('devices', function(Blueprint $table) {
            $table->increments('id');
            $table->string('manufacturer', 25);
            $table->string('device', 25);
            $table->string('model', 25);
            $table->string('tag', 25);
            $table->string('macAddress',17);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('devices');
    }
}

statuses table

<?php

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

class CreateStatusesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('statuses', function (Blueprint $table) {
            $table->string('status', 20);
            $table->text('note', 300);
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('statuses');
    }
}


inventories table


<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateInventoriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('inventories', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('employee_id')->unsigned();
            $table->integer('location_id')->unsigned();
            $table->integer('device_id')->unsigned();
            $table->foreign('employee_id')->references('id')->on('employees')->onDelete('cascade');
            $table->foreign('location_id')->references('id')->on('locations')->onDelete('cascade');
            $table->foreign('device_id')->references('id')->on('devices')->onDelete('cascade');
            $table->timestamps();
        });
    }
    
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    
    public function down()
    
    {
    
        Schema::table('inventories', function(Blueprint $table)
        {
            $table->dropForeign(['employee_id_foreign']);
            $table->dropColumn('employee_id');
            $table->dropForeign(['location_id']);
            $table->dropColumn('location_id');
            $table->dropForeign(['device_id']);
            $table->dropColumn('device_id');
    
    
    
        });
            Schema::drop('inventories');
    }
}

Devon

From what I'm seeing in the error, you're attempting to insert a row that doesn't assign a value to any of employee_id, location_id, device_id and the fields are not ->nullable()... the very columns that have foreign key checks on them.

I'd say you'd need to assign them a value or make them NULL.

afonte15

This is my InventoriesController.php

<?php
namespace  App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
use App\Inventory;
use App\Http\Requests\InventoryRequest;
use App\User;
class InventoriesController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth');  //does not allow users to login, redirects back to login when using LDAP credentials
    }
    public function index(Request $request)
    {
        $location = $request->input("building");
        if ($location != null) {
            $inventories = Inventory::where('building', $location)->get();
        }  else {
            $inventories = Inventory::all();
        }
        return view('computers.index', compact('inventories'));
    }
    public function show($inventories)
    {
        $inventories = Inventory::findOrFail($inventories);
        return view::make('computers.show')
            ->with('inventory', $inventories);
    }
    public function create(){
        //flash('Hello World', 'This is the message');
        $user = User::current();
        return view('computers.create',['user' => $user]);// add back in parenthesis
    }
    /**
     * Store a newly created resource in storage.
     *
     * @param  inventory  $request
     * @return Response
     *
     */
    public function store(InventoryRequest $request)
    {
        Inventory::create($request->all());
        flash('Success!', 'Inventory Successfully Updated!');
//
//        return redirect()->back();  //temporary
        return back();
    }
    public function edit($inventories)
    {
        $inventories = Inventory::findOrFail($inventories);
        return view('computers.edit', compact('inventories'));
    }
    public function update(InventoryRequest $request, Inventory $inventories){
        $inventories->update($request->all());
        flash('Success!', 'Inventory Successfully Updated!');
        return back();
    }
    public function search()
    {
        $search = \Request::get('q'); //<-- we use global request to get the param of URI
//            $search = Input::get('search');
        $inventories = Inventory::where('lastName','LIKE','%'.$search.'%')
            -> orwhere('firstName', 'LIKE','%'.$search.'%' )
            -> orwhere('department', 'LIKE','%'.$search.'%' )
            -> orwhere('building', 'LIKE','%'.$search.'%' )
            -> orwhere('room', 'LIKE','%'.$search.'%' )
            -> orwhere('manufacturer', 'LIKE','%'.$search.'%' )
            -> orwhere('device', 'LIKE','%'.$search.'%' )
            -> orwhere('model', 'LIKE','%'.$search.'%' )
            -> orwhere('tag', 'LIKE','%'.$search.'%' )
            -> orwhere('macAddress', 'LIKE','%'.$search.'%' )
            -> orwhere('status', 'LIKE','%'.$search.'%' )
            -> orwhere('notes', 'LIKE','%'.$search.'%' )
            ->get();
        return view('computers.search',compact('inventories'));
    }
}

Devon
  1. You're not doing any sort of validation on the request data.
  2. Your request data keys probably don't align with your database column names, dd($request->all()) to see.
  3. Ensure that you have set employee_id, location_id, and device_id as $fillable in your Inventory model.
Dunsti

I guess the errors above occure, when you try artisan migrate ?

Put the $table->timestamps() before the $table->foreign(...) in your CreateInventoriesTable

public function up()
    {
        Schema::create('inventories', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('employee_id')->unsigned();
            $table->integer('location_id')->unsigned();
            $table->integer('device_id')->unsigned();

            $table->timestamps();

            $table->foreign('employee_id')->references('id')->on('employees')->onDelete('cascade');
            $table->foreign('location_id')->references('id')->on('locations')->onDelete('cascade');
            $table->foreign('device_id')->references('id')->on('devices')->onDelete('cascade');
        });
    }
afonte15

@Dunsti I was able to get the database to drop and migrate. My issue is data does not submit to the database and I get the above error.

afonte15

@Devon I do have models set up and prior to making the change to multiple migrations I had only a Inventories.php and data did sumbit to the database. Now I have 5 models which are listed below. I am still getting the error I pasted above.

Employee Model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Employee extends Model
{
    protected $fillable = [
        'lastName',
        'firstName',
        'department'
    ];
}

Location Model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Employee extends Model
{
    protected $fillable = [
        'building',
        'room'
    ];
}

device model


<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Device extends Model
{
    protected $fillable = [
        'manufacturer' ,
        'device',
        'model',
        'tag',
        'macAddress'
        ];
}


Status Model


<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Status extends Model
{
    protected $fillable = [
        'status',
        'note',
    ];
}

Inventory Model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Inventory extends Model
{
    /**
     *Fillable fields for Inventory
     *
     * @var array
     */
    protected $fillable = [
        'employee_id',
        'location_id',
        'device_id'
    ];

}

Devon

Post your Inventory model here, please.

Please sign in or create an account to participate in this conversation.