CODEWITHSUNDEEP
laraveldatabase-migration
sinak
sinak

Reputation: 242

foreign key laravel migration

This is my students migration and I want to have foreign key in it named subject_id:

public function up()
{
    Schema::create('students', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('first_name');
        $table->string('last_name');
        $table->string('student_code');
        $table->string('national_code');
        $table->integer('subject_id')->unsigned();
        $table->foreign('subject_id')
            ->references('id')->on('subjects');
        $table->timestamps();
    });
}

and this is my subjects migation:

public function up()
{
    Schema::create('subjects', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('title');
        $table->timestamps();
    });
}

My problem is very simple I searched in document and found nothing except my migration codes. I am confused. anyway first of all I ran subjects migration script then students but I get strange error:

Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table students add constraint students_subject_id_foreign foreign key (subject_id) references subjects (id))

Upvotes: 0

Views: 50

Answers (3)

maximkou
maximkou

Reputation: 5332

Looks like your problem is in subject_id definition. Local and foreign key must be same type, in our case:

$table->unsignedBigInteger('subject_id');

See https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html:

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

Upvotes: 1

Tarasovych
Tarasovych

Reputation: 2398

So, your subjects table id has BIG INT datatype,subject_id should have the same data type.

Use $table->unsignedBigInteger('subject_id'); instead.

Reference:

a foreign key column must have the same data type + the same length + the same scale as the corresponding referenced column

Upvotes: 1

Lakhwinder Singh
Lakhwinder Singh

Reputation: 5582

Problem is with your fields defining order. You have to add all your fields first then add foreign keys.

Try below code:-

public function up()
{
    Schema::create('students', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('first_name');
        $table->string('last_name');
        $table->string('student_code');
        $table->string('national_code');
        $table->integer('subject_id')->unsigned();
        $table->timestamps();

        $table->foreign('subject_id')
            ->references('id')->on('subjects');
    });
}

Upvotes: 0

Related Questions