CODEWITHSUNDEEP
mysqllaravelforeign-keys
Jeremy Harris
Jeremy Harris

Reputation: 24549

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]: General error: 1215 Cannot add foreign key constraint (SQL: alter table products add constraint products_provider_id_foreign foreign k ey (provider_id) references id (providers) on delete cascade)

The MySQL SHOW ENGINE INNODB STATUS; result under Latest Foreign Key Error is:

2016-02-22 21:38:17 11debc000 Error in foreign key constraint of table testdb/#sql-186_18db: foreign key (provider_id) references id (providers) on delete cascade: Cannot resolve table name close to: (providers) on delete cascade

I've checked all of the following:

The first table is providers and has a primary key id defined as INT(10) and unsigned:

Providers Table

The second table is products and has a column called provider_id defined as INT(10) and unsigned.

Products Table

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:

EDIT

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');
});

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');

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.

Upvotes: 1

Views: 2352

Answers (1)

Jilson Thomas
Jilson Thomas

Reputation: 7303

Change this: 

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');
});

to 

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.

Upvotes: 4

Related Questions