Laravel 4.2 many to many relation, use something else then the default ID

Question

I have 2 models and both they aren't using the ID from the table, but the field internal_id. So i customized my pivot schema but i got stuck on connecting them. Im getting the error:

General error: 1215 Cannot add foreign key constraint (SQL: alter table `seoshop_category_product` add constraint seoshop_category_product_category_id_foreign foreign key   
  (`category_id`) references `seoshop_categories` (`internal_id`) on delete cascade)                                                                                                            

The code for the migration is:

Schema::create('seoshop_category_product', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('category_id')->unsigned()->index();
            $table->foreign('category_id')->references('internal_id')->on('seoshop_categories')->onDelete('cascade');
            $table->integer('product_id')->unsigned()->index();
            $table->foreign('product_id')->references('internal_id')->on('seoshop_products')->onDelete('cascade');
            $table->timestamps();
        });

Both fields as seoshop_products.internal_id as seoshop_categories.internal_id are existing, column types are both int(11).

Can someone tell me what is going wrong?

Migrations for the tables seoshop_categories and seoshop_products

//seoshop_products
public function up()
    {
        Schema::create('seoshop_products', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('shop_id');
            $table->integer('internal_id')->signed()->index();
            $table->integer('internal_variant_id');
            $table->string('visible');
            $table->string('tags');
            $table->timestamps();
        });
    }


//Table seoshop_categories
public function up()
    {
        Schema::create('seoshop_categories', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('internal_id')->signed()->index();
            $table->datetime('seoshop_created_at');
            $table->datetime('seoshop_updated_at');
            $table->text('full_description');
            $table->timestamps();
        });
    }

Okay so now i've create my table, and its working as how it should. I need to get my product with categories (many-2-many). So i use

SEOshopProduct::find(1)->with('categories')->get();

After a dd() the categories are empty and i've looked into my query how it is called:

[8] array(3) {
["query"] "select `seoshop_categories`.*, `seoshop_category_product`.`product_id` as `pivot_product_id`, `seoshop_category_product`.`category_id` as `pivot_category_id` from `seoshop_categories` inner join `seoshop_category_product` on `seoshop_categories`.`id` = `seoshop_category_product`.`category_id` where `seoshop_category_product`.`product_id` in (?)"
["bindings"] array(1) {
[0] 8
}
["time"] 0.37
}

The internal_id's of both products and categories is greater then 10.000 and i dont see it back in the query.

My models:

Product:

public function categories(){
        return $this->belongsToMany('SEOshopCategory', 'seoshop_category_product', 'product_id', 'category_id');
    }

Categories:

public function products(){
        return $this->belongsToMany('SEOshopCategory', 'seoshop_category_product', 'category_id', 'product_id');
    }

Answer 1

To setup the foreign key constraint, the field definitions need to match exactly. In this case, however, the seoshop_category_product.category_id field is defined as an UNSIGNED INT, but the referenced seoshop_categories.internal_id field is defined as a SIGNED INT. The same is true for the foreign key for your products.

So, you can either update the internal_id fields on your categories and products tables to be unsigned, or you can update your foreign key fields on your pivot table to be signed.