MySQL外键-无法解析表名 [英] MySQL Foreign Key - Cannot Resolve Table Name Close TO
问题描述
我遇到了一个外键问题，这确实使我感到困惑.运行迁移时遇到的具体错误是:
I've run into a foreign key issue that really has me stumped. The specific error I get when running my migration is:
SQLSTATE [HY000]:常规错误:1215无法添加外键约束(SQL:更改表
products添加约束product_provider_id_foreign外键 ey(provider_id)在删除级联上引用id(providers)
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table
productsadd constraint products_provider_id_foreign foreign k ey (provider_id) referencesid(providers) on delete cascade)
最新外键错误下的MySQL SHOW ENGINE INNODB STATUS;结果是:
The MySQL SHOW ENGINE INNODB STATUS; result under Latest Foreign Key Error is:
2016-02-22 21:38:17 11debc000表testdb/#sql-186_18db的外键约束错误: 外键(
provider_id)在删除级联上引用id(providers): 无法解析接近以下名称的表名: (providers)删除级联上
2016-02-22 21:38:17 11debc000 Error in foreign key constraint of table testdb/#sql-186_18db: foreign key (
provider_id) referencesid(providers) on delete cascade: Cannot resolve table name close to: (providers) on delete cascade
我已经检查了以下所有内容:
I've checked all of the following:
- 相同的数据类型:
int - 相同数据长度:
10 - 相同签名:
unsigned - 目标表是键:
primary - 两个表均能正确使用引擎:
INNODB - 两个表都具有相同的字符集:
utf8 - 两个表都使用相同的排序规则:
utf8
- Same data type:
int - Same data length:
10 - Same signing:
unsigned - The target table is a key:
primary - Both tables correct engine:
INNODB - Both tables same charset:
utf8 - Both tables same collation:
utf8
第一个表是providers，并具有定义为INT(10)且未签名的主键 id :
The first table is providers and has a primary key id defined as INT(10) and unsigned:
第二个表是products，有一列称为provider_id的列定义为INT(10)，并且是无符号的.
The second table is products and has a column called provider_id defined as INT(10) and unsigned.
当然已经记录了此错误，尤其是在SO上，但没有什么常见的东西(
This error is of course well documented, especially on SO, but none of the common things (including this checklist) are working. Does anyone see anything wrong here or have any other suggestions?
关于它的价值，我的系统:
For what it is worth, my system:
- OSX 10.8.5
- MySQL 5.6.11
- 迁移在Laravel 5.2中进行(尽管这与问题有关)
Laravel迁移中添加外键的相关部分是:
The relevant portion of the Laravel migration to add the foreign key is:
Schema::table('products', function (Blueprint $table) {
$table->foreign('provider_id')->references('providers')->on('id')->onDelete('cascade');
$table->foreign('brand_id')->references('brands')->on('id')->onDelete('cascade');
});
products表的创建开始如下:
The creation of the products table starts like this:
public function up()
{
Schema::create('products', function(Blueprint $table) {
$table->increments('id');
$table->integer('provider_id')->unsigned();
提供商迁移的相关部分是:
And the relevant part of the providers migration is:
public function up()
{
Schema::create('providers', function(Blueprint $table) {
$table->increments('id');
我本来是使用Schema::create(...)创建表的外键部分，但是当它不起作用时，我尝试使其在创建后 出现，以防万一出于某种原因正确构建迁移.
I originally had the foreign key part of the table creation with Schema::create(...) but when it wasn't working, I tried making it come after the creation in case for some reason it wasn't building the migration correctly.
推荐答案
更改此内容:
Schema::table('products', function (Blueprint $table) {
$table->foreign('provider_id')->references('providers')->on('id')->onDelete('cascade');
$table->foreign('brand_id')->references('brands')->on('id')->onDelete('cascade');
});
到
Schema::table('products', function (Blueprint $table) {
$table->foreign('provider_id')->references('id')->on('providers')->onDelete('cascade');
$table->foreign('brand_id')->references('id')->on('brands')->onDelete('cascade');
});
您已将位置交换为列名和表.
You've swapped the position for column name and table.
这篇关于MySQL外键-无法解析表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
