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

score 2 · Accepted Answer · answered Nov 23 '15 at 14:47

2

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.

answered Nov 23 '15 at 14:47

patricus

59,488
15
143
145

Thanks Patricus, that makes sense. But how do i need to set up my new migration, or edit it? – Donny van V Nov 23 '15 at 15:16
Would added ->signed()->index(); be enough to my migrations for seoshop_products.internal_id and for the categories? – Donny van V Nov 23 '15 at 15:20
@DonnyvanV If you want to change your new pivot table to match your existing tables, just remove the call to `->unsigned()` on your category_id and product_id fields. – patricus Nov 23 '15 at 15:21
You can reference [this link as well](http://stackoverflow.com/questions/15534977/mysql-cannot-add-foreign-key-constraint) – Basheer Kharoti Nov 23 '15 at 15:23
@patricus i tried to but that didnt worked out. Luckely for me that i just created the tables and they didnt had data – Donny van V Nov 23 '15 at 15:26
@patricus i've added my models, can you tell me why i don't get any results while both ID's are existing? – Donny van V Nov 23 '15 at 15:47
1

@DonnyvanV Please post this as a separate question. That will help future people who come searching for answers, and it will help you get more eyes on the issues you're facing. – patricus Nov 23 '15 at 17:17

score -1 · Answer 2 · answered Nov 23 '15 at 14:05

-1

You can tell Laravel what the local and foreign keys are when you define the relationship in your model...

class Product extends Eloquent
{
    public function categories() {
        return $this->hasMany('Category', 'internal_id', 'id');
    }
}

class Category extends Eloquent
{
    public function products() {
        return $this->hasMany('Product', 'internal_id', 'id');
    }
}

answered Nov 23 '15 at 14:05

Tim Sheehan

3,994
1
15
18

Yeah i know that but its not with relating the model.php files, but drawing my table structure... Using a migration and then run artisan migrate gets me into trouble with the error above – Donny van V Nov 23 '15 at 14:06
I'm just trying to figure out why you're trying to define these as foreign keys in your DB when Laravel provides you with a method to use alternate foreign keys out of the box within your model? – Tim Sheehan Nov 23 '15 at 14:10
Isnt it a extra security detail to prevent it if something went really bad, and by then i mean really really bad :P – Donny van V Nov 23 '15 at 15:51

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

2 Answers2