SQLSTATE[HY000]: General error: 1005 Can't create table

Published 6 days ago by jashnkrish

Best Answer (As Selected By jashnkrish)
RamjithAp

Do below checks & see it helps

  1. Make sure your user's table migration happen before q&a table because q&a migration file referencing users table so it has to be present. Usually, laravel executes migration files based on the date created so better consider executing user table first and then q&a.
  2. In case of foreign keys, the referenced and referencing fields must have exactly the same data type. Check your user's table id & foreign key has same data type like unsigned() etc.
  3. Make sure your user's tables engine (InnoDB) and q&a table engine are same.If anyone one of this table is MyISAM it won't work.

Still can't figure it out. Post your user's table migration codes.

saurabhd

Please follow this link

jashnkrish

i have referred it a minute ago.. Still its not helpful to me

RamjithAp

Could you please show us your migration table codes.

jashnkrish

I'm getting this error while migrating .. Create qna schema

<?php

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

class CreateQnaSchema extends Migration
{
    const SCHEMA = "" ;

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $schema =  static::SCHEMA ;

        Schema::create($schema . 'questions', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('user_id');
            $table->string('question');
            $table->string('level')->default('Beginner');
            $table->boolean('solved')->default(false);
            $table->timestamps();
            $table->foreign('user_id')->references('id')->on('users');
        });

        Schema::create($schema . 'answers', function(Blueprint $table) use ($schema)
        {
            $table->increments('id');
            $table->integer('user_id');
            $table->integer('question_id');
            $table->text('answer');
            $table->timestamps();
            $table->foreign('user_id')->references('id')->on('users');
            $table->foreign('question_id')->references('id')->on($schema . 'questions');
        });

        Schema::create($schema . 'votes', function(Blueprint $table) use ($schema)
        {
            $table->increments('id');
            $table->integer('user_id');
            $table->integer('question_id')->nullable();
            $table->integer('answer_id')->nullable();
            $table->integer('vote');
            $table->timestamps();
            $table->foreign('user_id')->references('id')->on('users');
            $table->foreign('question_id')->references('id')->on($schema . 'questions');
            $table->foreign('answer_id')->references('id')->on($schema . 'answers');
        });

        Schema::create($schema . 'tags', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name');
            $table->string('display');
            $table->timestamps();
            $table->softDeletes();
        });


        Schema::create($schema . 'tags_questions', function(Blueprint $table) use ($schema)
        {
            $table->increments('id');
            $table->integer('tag_id');
            $table->integer('question_id');
            $table->timestamps();
            $table->foreign('tag_id')->references('id')->on($schema . 'tags');
            $table->foreign('question_id')->references('id')->on($schema . 'questions');
            $table->unique(['tag_id', 'question_id']);
        });

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        $schema = static::SCHEMA ;
        Schema::dropIfExists($schema . 'tags_questions');
        Schema::dropIfExists($schema . 'tags');
        Schema::dropIfExists($schema . 'votes');
        Schema::dropIfExists($schema . 'answers');
        Schema::dropIfExists($schema . 'questions');
    }
}
 

Create notification table

<?php

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

class CreateNotificationsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('notifications', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->string('type');
            $table->morphs('notifiable');
            $table->text('data');
            $table->timestamp('read_at')->nullable();
            $table->timestamps();
        });
    }

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

Create notification foreign key

<?php

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

class NotificationsForeignKey extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('notifications', function (Blueprint $table) {
            $table->foreign('notifiable_id')
                ->references('id')->on('users')
                ->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
    }
}
jashnkrish

This is my total error

E:\Projects\Laravel projects\Laravel-QuestionAnswer>php artisan migrate Migration table created successfully.

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table questionanswer.#sql-269c_114 (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table questions add constrain t questions_user_id_foreign foreign key (user_id) references users (id))

[PDOException] SQLSTATE[HY000]: General error: 1005 Can't create table questionanswer.#sql-269c_114 (errno: 150 "Foreign key constraint is incorrectly formed")

jashnkrish

Im trying to work with this application..

https://learninglaravel.net/a-free-question-answer-app-built-using-laravel-5

But i'm facing problem while migrating the files...

RamjithAp

Do below checks & see it helps

  1. Make sure your user's table migration happen before q&a table because q&a migration file referencing users table so it has to be present. Usually, laravel executes migration files based on the date created so better consider executing user table first and then q&a.
  2. In case of foreign keys, the referenced and referencing fields must have exactly the same data type. Check your user's table id & foreign key has same data type like unsigned() etc.
  3. Make sure your user's tables engine (InnoDB) and q&a table engine are same.If anyone one of this table is MyISAM it won't work.

Still can't figure it out. Post your user's table migration codes.

jashnkrish

Thank you for helping me out

Please sign in or create an account to participate in this conversation.