Laravel: Cannot add foreign keys constraints on production

Question

I’m developing a Laravel project and I created the models, when I run the migrations on my computer (where I’m developing) everything works fine, even the relations between tables. The problem comes when I upload the code to the production server and when I run the migrations where I create the relations, I got an error.

In the code below I leave some commented lines, those lines are things that I tried to do to solve the problem, but nothing worked.

I tried to set the table engine to innoDB on both tables, but it didn’t work.
I tried to set the id datatype as increments on both tables, and the column deviceTypeID that is going to serve as foreign key, I defined it as unsigned and integer and it didn’t work.
I even checked the PHP, Composer, Doctrine/dbal and mysql versions on both computers and are the same (or almosr the same).

Developing Computer:
PHP: 7.3
Composer: 1.8.6
MySQL Server: 5.7.23
Doctrine/dbal: 2.9

Production Server:
PHP: 7.3
Composer: 1.9
MySQL Server: 5.7.25
Doctrine/dbal: 2.9
(It runs Ubuntu Server 18.04)

I also tried to run the project on another computer an see if I got the same error, but it didn't come, everything worked fine.
I also ran this query "alter table devices add constraint devices_devicetypeid_foreign foreign key (deviceTypeID) references devicestype(id) on delete cascade" without all the quotes directly on mysql command line and it worked. So the problem is not related to the user privileges.

It always crash on production when it tries to run the code on AddRelationDevicesDeviceType

class CreateDevicesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('devices', function (Blueprint $table) {
            //$table->engine = 'innoDB'
            $table->bigIncrements('id');
            //$table->increments('id');
            $table->unsignedBigInteger('deviceTypeID');
            //$table->integer('deviceTypeID')->unsigned();
            $table->timestamps();
        });
    }
}

class CreateDevicesTypeTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('devicesType', function (Blueprint $table) {
           //$table->engine = 'innoDB'
            $table->bigIncrements('id');
            //$table->increments('id');
            $table->string("Device Type");
            $table->text("Description");
            $table->timestamps();
        });
    }
}


class AddRelationDevicesDeviceType extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('devices', function($table) {
            $table->foreign('deviceTypeID')
                            ->references('id')->on('devicesType')
                            ->onDelete('cascade');
        });
    }
}

This is the error:

Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `devices` add constraint `devices_devicetypeid_foreign` foreign key (`deviceTypeID`) references `devicestype`(`id`) on delete cascade) at /var/www/html/MYPROJECT/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the databa se's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668|

  Exception trace:

  1   Doctrine\DBAL\Driver\PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint") /var/www/html/MYPROJECT/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119

  2   PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint") /var/www/html/MYPROJECT/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117

Welcome to Stack Overflow! Could you format your question a little better? Newlines are helpful to help people read, and code should be in codeblocks. See our Markdown Editing Help guide! You seem to be missing spaces before your dashes -- that'll make them bullet points. — Cullub
– Cullub, Commented Aug 8, 2019 at 20:35
Both columns type need to match. Did you tried used bigInteger/bigIncrements on both? — Felippe Duarte
– Felippe Duarte, Commented Aug 8, 2019 at 20:39
You can run SHOW ENGINE INNODB STATUS; command in your database and see the exact error, then show us. — Felippe Duarte
– Felippe Duarte, Commented Aug 8, 2019 at 20:42
There's most likely a different between your development and production database. You can figure out by copying over the production database to your development environment and execute the same migration there. I bet you'll get the same error there. — PtrTon
– PtrTon, Commented Aug 9, 2019 at 4:21
Ok now you have the error Cannot resolve table name.. double check table names. MySQL can be case sensitive, depending on the configuration. — Felippe Duarte
– Felippe Duarte, Commented Aug 9, 2019 at 14:18

Candy · Accepted Answer · 2019-08-09 20:01:19Z

I I figured out how to solve my problem.

The problem was when I ran the first migration where I create the table “devicesType” MySQL changed the table name to lower case, so it ended up being “devicestype” and when I tried to refer that table in the last migration where I create the relation Laravel couldn’t find the table.

My development server wasn’t case sensitive while my production server was case sensituve.

I also change the "deviceTypeID" from "unsignedBigInteger" to "bigInteger" and I specify the the type unsigned using the method "unsigned"

This is my new code if is useful to someone:

class CreateDevicesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('devices', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('deviceTypeID')->unsigned();
            $table->timestamps();
        });
    }
}

class CreateDevicesTypeTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('devicestype', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string("Device Type");
            $table->text("Description");
            $table->timestamps();
        });
    }
}


class AddRelationDevicesDeviceType extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('devices', function($table) {
            $table->foreign('deviceTypeID')
                            ->references('id')->on('devicestype')
                            ->onDelete('cascade');
        });
    }
}

Collectives™ on Stack Overflow

Laravel: Cannot add foreign keys constraints on production

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related