Laravel Migration Foreignkey Error

Question

Laravel 5.6 running on: PHP 7.2, MariaDB 10.3

When I want to set foreign key for my table, I just keep taking that error.

On other tables, all id variables defined by Laravel itself and auto increments unsigned

So, my Migration is like this:

public function up()
{

    Schema::create('user_roles', function (Blueprint $table) {
        $table->increments('id');
        $table->unsignedInteger('role_id');
        $table->unsignedInteger('user_id');

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

The error is like this:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') on delete cascade' at lin e 1 (SQL: alter table user_roles add constraint user_roles_role_id_foreign foreign key (role_id) references roles () on delete cascade)

Errors thrown by Laravel:

1 PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') on delete cascade' at line 1") C:\xampp\htdocs\order-project\vendor\laravel\framework\src\Illuminate\Database\Connection.php:452

2 PDO::prepare("alter table user_roles add constraint user_roles_role_id_foreign foreign key (role_id) references roles () on delete cascade") C:\xampp\htdocs\order-project\vendor\laravel\framework\src\Illuminate\Database\Connection.php:452

Other tables / migrations to run properly right? So nothing wrong with the setup? — utdev
– utdev, Commented Aug 24, 2018 at 19:50
Seems like Laravel is ignoring the ->references('id') for some reason. Without cascade, does it work? — Phiter
– Phiter, Commented Aug 24, 2018 at 19:51
@utdev Yes, all the other migrations running without any errors. @AlvaroAlves, I was already tried that and it gives the error like " PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table order-project.#sql-3228_8a (errno: 150 "Foreign key constraint is incorrectly formed")")" @Phiter Nope, it's not working either. By the way, Php Storm says "Method 'references' not found in Illuminate\Support\Fluent" but i think it is about the IDE not about the laravel be cause when i was work with VSC there is no error like that. — Ahmet Çelikezer
– Ahmet Çelikezer, Commented Aug 24, 2018 at 19:54
you could just test following stackoverflow.com/questions/31263637/… this will return your migration query as a raw sql query. Just to be sure that the query is correct — utdev
– utdev, Commented Aug 24, 2018 at 19:56

Alexander Villalobos · Accepted Answer · 2018-08-24 20:05:46Z

0

try this

$table->integer('role_id')->unsigned();
$table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');

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

answered Aug 24, 2018 at 20:05

Alexander Villalobos

4613 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ahmet Çelikezer Over a year ago

It doesn't work for me, i got an error like: General error: 1005 Can't create table order-project.#sql-410_a (errno: 150 "Foreign key constraint is incorrectly formed")")

Mustafa Salim · Accepted Answer · 2018-08-24 22:05:16Z

0

I think you have similiar problem with this post Laravel foreign key onDelete('cascade') not working

Hope this can help

answered Aug 24, 2018 at 22:05

Mustafa Salim

216 bronze badges

2 Comments

Ahmet Çelikezer Over a year ago

Thanks, but it doesn't work either. Error is: errno: 150 "Foreign key constraint is incorrectly formed")

Erubiel Over a year ago

Did you change the engine to InnoDB as suggested in the question?

Ahmet Çelikezer · Accepted Answer · 2018-08-25 13:00:17Z

0

I solved the problem. It fails because of Laravel trying to migrate user_roles table before the roles table. I just migrate roles table before the user_roles table and it's worked! Before the solution rules table is not throwing an error but it's not completely created.

answered Aug 25, 2018 at 13:00

Ahmet Çelikezer

377 bronze badges

Collectives™ on Stack Overflow

Laravel Migration Foreignkey Error

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related