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, Aug 24 '18 at 19:50
Seems like Laravel is ignoring the `->references('id')` for some reason. Without cascade, does it work? — Phiter, Aug 24 '18 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, Aug 24 '18 at 19:54
you could just test following https://stackoverflow.com/questions/31263637/how-to-convert-laravel-migrations-to-raw-sql-scripts this will return your migration query as a raw sql query. Just to be sure that the query is correct — utdev, Aug 24 '18 at 19:56
@utdev Thanks, i just run that command and tried to run following SQL in my local DB, the error was like this: 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 'alter table `user_roles` add constraint `user_roles_role_id_foreign` foreign key' at line 2 SQL: alter table `user_roles` add constraint `user_roles_role_id_foreign` foreign key (`role_id`) references `roles` (`id`) Where is the problem? I don't have much knowledge about DB processes. — Ahmet Çelikezer, Aug 25 '18 at 12:06
Try Composer update or composer dump-autoload Also make sure your foreign table upload after user table in migration table.... Simply try changing order — Vipertecpro, Aug 25 '18 at 15:48
@ViperTecPro Thanks for your suggestion, I solved the problem and shared to the below. — Ahmet Çelikezer, Aug 26 '18 at 20:30

score 0 · Answer 1 · answered Aug 24 '18 at 20:05

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 '18 at 20:05

Alexander Villalobos

461
3
11

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")") – Ahmet Çelikezer Aug 25 '18 at 11:51

score 0 · Answer 2 · answered Aug 24 '18 at 22:05

0

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

Hope this can help

answered Aug 24 '18 at 22:05

Mustafa Salim

21
6

Thanks, but it doesn't work either. Error is: errno: 150 "Foreign key constraint is incorrectly formed") – Ahmet Çelikezer Aug 25 '18 at 11:55
Did you change the engine to InnoDB as suggested in the question? – Erubiel Aug 25 '18 at 13:07

score 0 · Accepted Answer · answered Aug 25 '18 at 13:00

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.

Laravel Migration Foreignkey Error

3 Answers3