CODEWITHSUNDEEP
phpmysqllaravellaravel-5eloquent
Ahmed Adel
Ahmed Adel

Reputation: 39

150 "Foreign key constraint is incorrectly formed"

I have a problem with migrating my table.

I have a users table that has a string id and the table was created before with SQL without a migration.

Later on, I created a table called surveys that has a foreign key to user_id with the following code .

hema::create('surveys', function (Blueprint $table) {
            $table->increments('id');
            $table->string('user_id',40);
            $table->foreign('user_id')->references('id')->on('users') ->onUpdate('cascade')->onDelete('cascade');
            $table->string('gender');
            $table->string('age');
            $table->string('education');
            $table->string('proficiency');
            $table->string('behaviour');
            $table->timestamps();
        });

I am always getting the below error whenever I try to migrate it and I have no clue why is this happening. The id in table users is varchar 40 and so is the user_id.


  SQLSTATE[HY000]: General error: 1005 Can't create table `d0372341`.`surveys` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `surveys` add constraint `surveys_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade on update cascade)

  at D:\xampp install\htdocs\game-db\vendor\laravel\framework\src\Illuminate\Database\Connection.php:692
    688▕         // If an exception occurs when attempting to run a query, we'll format the error
    689▕         // message to include the bindings with SQL, which will make this exception a
    690▕         // lot more helpful to the developer instead of just the database's errors.
    691▕         catch (Exception $e) {
  ➜ 692▕             throw new QueryException(
    693▕                 $query, $this->prepareBindings($bindings), $e
    694▕             );
    695▕         }
    696▕     }

So if you can please help me with this I would really appreciate it.

Upvotes: 0

Views: 664

Answers (2)

Atika
Atika

Reputation: 1067

So I post the answer here to help others : (ps. don't forget to mark this answer as the best answer)

In your users table, verify that the collation of your id field is utf8mb4_unicode_ci. The error can sometimes be due to this.

Upvotes: 1

Othmane
Othmane

Reputation: 179

Change $table->string('user_id',40); to $table->unsignedBigInteger('user_id)

Or if you're using Laravel 8.x you can basically do

$table->foreignId("user_id")->constrained()->cascaseOnDelete();

Then run this command

php artisan migrate:refresh

Edit:

Make sure the primary key in your users table is string then update your User model

public $incrementing = false;
protected $keyType = 'string';

https://laravel.com/docs/8.x/eloquent#primary-keys

Upvotes: 0

Related Questions