Laravel foreign keys don' work in migration

huangapple go评论65阅读模式
英文:

Laravel foreign keys don' work in migration

问题

我试图在Laravel迁移中链接两个表格,但是一直出现General error: 1215错误,我不知道为什么。

这是我试图链接到另一个表格的代码。

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('exercise', function (Blueprint $table) {
            $table->bigIncrements('oefNr');
            $table->char('oefening_naam');
            $table->char('uitleg');
            $table->integer('videoNr')->foreign();
            $table->char('accountNr');
        });
    }

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

这是我试图链接到的另一个表格的代码。

我还尝试过改变键的位置,将其放在exercise而不是videos中。但问题是,它首先创建exercise表,然后我会得到不同的错误。因为它尝试链接到尚不存在的表格。

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'videoNr' (Connection: mysql, SQL: create table videos (videoNr bigint unsigned not null auto_increment primary key, filenaam char(255) not null, upload_datum timestamp not null default CURRENT_TIMESTAMP, oefNr int not null, videoNr bigint unsigned not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci'). 这是第一个错误代码。

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (Connection: mysql, SQL: alter table videos add constraint videos_videonr_foreign foreign key (videoNr) references videoNr (videoNr)) 我也遇到了这个错误消息。

英文:

I am trying to link 2 tables in a laravel migration. But I keep getting the General error: 1215. And I don't know why.

&lt;?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create(&#39;videos&#39;, function (Blueprint $table) {
            $table-&gt;bigIncrements(&#39;videoNr&#39;);
            $table-&gt;char(&#39;filenaam&#39;);
            $table-&gt;timestamp(&#39;upload_datum&#39;)-&gt;useCurrent();
            $table-&gt;integer(&#39;oefNr&#39;)-&gt;foreign();
            $table-&gt;foreign(&#39;videoNr&#39;)-&gt;references(&#39;videoNr&#39;)-&gt;on(&#39;videoNr&#39;);

        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists(&#39;videos&#39;);
    }
};

This is the code of the one I am trying to link to the other one.

&lt;?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create(&#39;exercise&#39;, function (Blueprint $table) {
            $table-&gt;bigIncrements(&#39;oefNr&#39;);
            $table-&gt;char(&#39;oefening_naam&#39;);
            $table-&gt;char(&#39;uitleg&#39;);
            $table-&gt;integer(&#39;videoNr&#39;)-&gt;foreign();
            $table-&gt;char(&#39;accountNr&#39;);
            
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists(&#39;exercise&#39;);
    }
};

This is the code for the one I am trying to link to.

I have also tried changing where the key is. So I put it in exercise instead of videos. But the problem with that is that it first creates the exercise table and then the I get a different error. Because it tries to link to a table that doesn't exists yet.

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'videoNr' (Connection: mysql, SQL: create table videos (videoNr bigint unsigned not null auto_increment primary key, filenaam char(255) not null, upload_datum timestamp not null default CURRENT_TIMESTAMP, oefNr int not null, videoNr bigint unsigned not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci'). This is the first error code.

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (Connection: mysql, SQL: alter table videos add constraint videos_videonr_foreign foreign key (videoNr) references videoNr (videoNr)) I have also encounterd this error message.

答案1

得分: 2

I have just found the awnser.
This code works.
I changed the where the foreign key was. And also changed my foreign to foreignId. And to change the order they migrate you can change the filename. Because they save the date as yyyymmddhhmmss, you can just put the make it so its created earlier.

英文:

I have just found the awnser.
This code works.
I changed the where the foreign key was. And also changed my foreign to foreignId. And to change the order they migrate you can change the filename. Because they save the date as yyyymmddhhmmss, you can just put the make it so its created earlier.

    &lt;?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create(&#39;videos&#39;, function (Blueprint $table) {
            $table-&gt;bigIncrements(&#39;videoNr&#39;);
            $table-&gt;char(&#39;filenaam&#39;);
            $table-&gt;timestamp(&#39;upload_datum&#39;)-&gt;useCurrent();
            $table-&gt;integer(&#39;oefNr&#39;);

        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists(&#39;videos&#39;);
    }
};
?&gt;
    &lt;?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create(&#39;exercise&#39;, function (Blueprint $table) {
            $table-&gt;bigIncrements(&#39;oefNr&#39;);
            $table-&gt;char(&#39;oefening_naam&#39;);
            $table-&gt;char(&#39;uitleg&#39;);
            $table-&gt;foreignId(&#39;videoNr&#39;)-&gt;references(&#39;videoNr&#39;)-&gt;on(&#39;videos&#39;); 
            $table-&gt;foreignId(&#39;accountNr&#39;)-&gt;references(&#39;accountNr&#39;)-&gt;on(&#39;account&#39;);     
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists(&#39;exercise&#39;);
    }
};

(edit I pasted my code instead of an image)

答案2

得分: 0

请注意:删除所有分配外键的 bigInteger,并使用以下方法:

假设您的关系如下:一个练习有“多个”视频。

在您的主表 exercise 中:

$table->id(); // 添加主键

在您的子表 videos 中:

$table->foreignId('exercise_id')->constrained();

如果您有模型,可以使用以下方法来添加外键:

$table->foreignIdFor(\App\Models\Exercise::class);

如果要使用当前方法创建外键关系,大整数和底层的 unsignedBigInteger 会如下所示:

$table->unsignedBigInteger('exercise_id');
$table->foreign('exercise_id')->references('id')->on('exercise');

希望这对您有帮助。

英文:

Note: remove all the bigIntegers that you're assigning foreign keys and use this:
Assuming that your relations are like -> An exercise has many videos.

In your parent table exercise:

$table-&gt;id();//add primary keys

in your child table videos:

$table-&gt;foreignId(&#39;exercise_id&#39;)-&gt;constrained();

if you have model then for adding foreign keys:

$table-&gt;foreignIdFor(\App\Models\Exercise::class);

For using your method the current one make foreign relations like this foreign keys are bigIncrements and under the hood unsignedBigInteger :

$table-&gt;unsignedBigInteger(&#39;exercise_id&#39;);
$table-&gt;foreign(&#39;exercise_id&#39;)-&gt;references(&#39;id&#39;)-&gt;on(&#39;exercise&#39;);

huangapple
  • 本文由 发表于 2023年5月25日 15:36:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/76329902.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定