英文:
Laravel foreign key problems
问题
如何处理外键关系，如果它不是一个 "id"？
我收到了一个错误：
1 vendor\laravel\framework\src\Illuminate\Database\Connection.php:539
PDOException::("SQLSTATE[42000]: 语法错误或访问违规: 1064 SQL 语法中的错误，检查与 MariaDB 服务器版本相对应的手册，以使用正确的语法在第 1 行使用")
2 vendor\laravel\framework\src\Illuminate\Database\Connection.php:539
PDO::prepare("alter table player_items add constraint player_items_items_code_foreign foreign key (items_code) references `` ()")
当我尝试这样做时：
Schema::create('player_items', function (Blueprint $table) {
$table->id();
$table->foreignId('player_id')->constrained();
$table->foreign('item_code');
$table->foreign('item_code')->references('code')->on('items');
$table->integer('state');
$table->timestamps();
});
错误出现在这些行：
$table->foreign('item_code');
$table->foreign('item_code')->references('code')->on('items');
这是因为我需要在这两个表之间建立关系，但我不能使用 "id"，我需要使用 "item" 表中的 "code"：
Schema::create('items', function (Blueprint $table) {
$table->id();
$table->string('code');
$table->string('name');
$table->string('description');
$table->integer('price');
$table->integer('minLevel');
$table->timestamps();
});
"item_code" 的示例可以是一个字符串：
'code' => 'balde01',
这种关系是否可行？还是我必须将 "code" 更改为整数？
只是... 我不想使用 "id" 建立关系，这正常吗？还是我只是疯了？
英文:
How to relation foreigkey if its not a "id" ?
I Get an error:
1 vendor\laravel\framework\src\Illuminate\Database\Connection.php:539
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 ')' at line 1")
2 vendor\laravel\framework\src\Illuminate\Database\Connection.php:539
PDO::prepare("alter table `player_items` add constraint `player_items_items_code_foreign` foreign key (`items_code`) references `` ()")
When i try this:
Schema::create('player_items', function (Blueprint $table) {
$table->id();
$table->foreignId('player_id')->constrained();
$table->foreign('item_code');
$table->foreign('item_code')->references('code')->on('items');
$table->integer('state');
$table->timestamps();
});
Error is on this lines:
$table->foreign('item_code');
$table->foreign('item_code')->references('code')->on('items');
Its because i need to refer a relationship between those two tables but i can't use the id, i need to use de "code" into item's table:
Schema::create('items', function (Blueprint $table) {
$table->id();
$table->string('code');
$table->string('name');
$table->string('description');
$table->integer('price');
$table->integer('minLevel');
$table->timestamps();
});
Example of what item's code can be (is a string):
'code' => 'balde01',
It posible this relation ? Or i have to change the "code" to integers ?
Its just.. i don't wanna relation with the id, this is normal? or im just crazy?
答案1
得分: 1
你编写的代码有错误。为什么有两个 $table->foreign('item_code')？如您在错误中所见：
PDO::prepare("alter table `player_items` add constraint `player_items_items_code_foreign` foreign key (`items_code`) references `` ()")
第一个 $table->foreign('item_code') 就已经产生了错误，所以不会达到第二个。它引用了空内容，因此产生了错误。您应该将其更改为以下内容：
$table->string('item_code');
$table->foreign('item_code')->references('code')->on('items');
$table->string('item_code') 用于创建表，而 $table->foreign('item_code') 用于向其添加约束。
并确保在创建 player_items 表之前创建 items 表。
如果 player_items 表在 items 表之前创建，您可以随时创建另一个迁移或在创建 items 表之后添加约束：
Schema::create('items', function (Blueprint $table) {
// 表的定义
});
Schema::table('player_items', function (Blueprint $table) {
// 假设已经声明了 $table->string('item_code')。如果没有，您可以在这里声明它。
$table->foreign('item_code')->references('code')->on('items');
});
英文:
The way you code it is wrong. Why do you have a 2 $table->foreign('item_code') ? As you can see at the error:
PDO::prepare("alter table `player_items` add constraint `player_items_items_code_foreign` foreign key (`items_code`) references `` ()")
its not reaching the second $table->foreign('item_code') since the first one produce the error. It referencing nothing so it producing an error.You should change it to this.
$table->string('item_code');
$table->foreign('item_code')->references('code')->on('items');
The $table->string('item_code') is to create the table. and the $table->foreign('item_code') is add a constraints to it.
and make sure that the items table will be created before the player_items table.
in case the player_items table will be created before the items table you can always create another migration or just add the constraints after the items table :
Schema::create('items', function (Blueprint $table) {
// tables
});
Schema::table('player_items' function (Blueprint $table) {
// Assuming that there is a $table->string('item_code') has been already declare. If not you can just declare it here.
$table->foreign('item_code')->references('code')->on('items');
});
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论