Laravel/SQL error when adding foreign keys

2
votes

I am trying to make some relationships between some tables but have an error that states..

Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 (SQL: alter table users add constraint users_board_id_foreign foreign key (board_id) references `` ())

class CreateUsersTable extends Migration
{
/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('email')->unique();
        $table->string('password');

        $table->foreign('board_id')->unsigned();
        $table->foreign('board_id')->references('id')->on('boards');

        $table->foreign('message_id')->references('id')->on('messages');
        $table->foreign('message_id')->unsigned();

        $table->rememberToken();
        $table->timestamps();
    });
}

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

This table is supposed to be related to this table...

<?php

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

class CreateBoardsTable extends Migration
{
/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('boards', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name', 100)->unique();

        $table->foreign('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users');

        $table->foreign('link_id')->unsigned();
        $table->foreign('link_id')->references('id')->on('links');

        $table->foreign('message_id')->unsigned();
        $table->foreign('message_id')->references('id')->on('messages');

        $table->foreign('tag_id')->unsigned();
        $table->foreign('tag_id')->references('id')->on('tags');

        $table->timestamps();

    });
}

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

I've already drop the table and the schema and started fresh and still get the same error! Please be kind :!

3
mysqllaravelreferenceconstraints

3 Answers

0
votes

Try this one: 

// USERS

class CreateUsersTable extends Migration
    {
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');

            $table->integer('board_id')->unsigned();
            $table->foreign('board_id')->references('id')->on('boards');

            $table->integer('message_id')->unsigned();
            $table->foreign('message_id')->references('id')->on('messages');


            $table->rememberToken();
            $table->timestamps();
        });
    }

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


    //BOARDS


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

    class CreateBoardsTable extends Migration
    {
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('boards', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name', 100)->unique();

            $table->integer('user_id')->unsigned();
            $table->foreign('user_id')->references('id')->on('users');

            $table->integer('link_id')->unsigned();
            $table->foreign('link_id')->references('id')->on('links');

            $table->integer('message_id')->unsigned();
            $table->foreign('message_id')->references('id')->on('messages');

            $table->integer('tag_id')->unsigned();
            $table->foreign('tag_id')->references('id')->on('tags');

            $table->timestamps();

        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('boards');
    }
}
0
votes

You can't define columns with foreign():

$table->integer('board_id')->unsigned();
$table->foreign('board_id')->references('id')->on('boards');

$table->integer('message_id')->unsigned();
$table->foreign('message_id')->references('id')->on('messages');

The same goes for the boards migration.

0
votes

I experienced very alike situation, my schema `
public function up() {

    Schema::dropIfExists('countries_states');
    Schema::create('countries_states', function (Blueprint $table) {
        $table->id();
        $table->foreignId('country_id')->unsigned();
        $table->foreign('country_id')->references('id')->on('countries');
        $table->string('state', 30);
        $table->string('abbr', 30);
    });
    //Schema::enableForeignKeyConstraints();
}

` And I had the same error screenshot

I solved it by granting access to specific user for specific database and voila - everything started to function like charm solution