Validate duplicate data againt one foreign key

Question

I have two table both table are merge in third table like this

**sub_category**
Id 
Name

**category**
ID
Name

**sub_cat**
ID
sub_id
cat_id

**category**                  **Sub_Category**
ID   Name                     ID        Name
1    One_Cat                   1        one_sub
2    two_Cat                   2        two_sub
3    three_Cat                 3        three_sub

**Cat_SubCat**
ID      Cat_id      sub_catId
1         1         1
2         1         2
3         1         3
4         1         1
5         2         1
6         2         2
7         2         2

Do you See under cat_id 1 there is duplicate of sub_CatId, and what i want is to validate that, under category one there should no duplicate subcategory same for category 2, there should no repeating subcategory under category 2

Is it a many-to-many relationship between categories and sub-categories? In other words, can a category have many different sub-categories and a single sub-category have more than one parent category? If not, do not use multiple tables to handle this. Both categories and sub-categories should be in the same table. You may want to check this question out: https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query. — Mike, Oct 28 '19 at 21:00

score 0 · Answer 1 · answered Oct 28 '19 at 20:46

If you have uniqueness on your names in the category and subcategory tables, then it means that the IDs will be unique always.. So instead of validating if the relationship already exists you can simply use, sync() or syncWithoutDetaching() which will ensure that there will always be one connection between a category and subcategory, and no duplicates. Otherwise you use attach() which produces duplicates.

Take a look at the docs for more

https://laravel.com/docs/master/eloquent-relationships#updating-many-to-many-relationships

Hi Nakov, i have added the table structure to explain my question. please have a look — Ali Raza, Oct 28 '19 at 21:28

Kerkouch · Answer 2 · 2019-10-28T21:04:37.240

The migration should look like this:

Schema::create('sub_cat', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedBigInteger('sub_id');
    $table->unsignedBigInteger('cat_id');

    $table->unique(['sub_id', 'cat_id']);
});

the unique index ensures the sub_id and cat_id are unique.

And then whenever you want to add a sub-category use sync or syncWithoutDetaching.

But why you're designing the tables like that, you should just use a parent_id on category table

Schema::create('category', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('category');
    $table->unsignedBigInteger('parent_id')->nullable;

    $table->foreign('parent_id')->references('id')->on('category');
});

The Category model then should look like this:

class Category extends Model
{
    public function parentCategory()
    {
        return $this->belongsTo(self::class, 'parent_id');
    }

    public function subCategories()
    {
        return $this->hasMany(slef::class, 'parent_id');
    }
}

i have added to table sturucture to explain my question further, please have a look — Ali Raza, Oct 28 '19 at 21:29
Can you include your models too in the question? So we can have an idea about what you are trying to achieve. — Kerkouch, Oct 28 '19 at 22:16

Validate duplicate data againt one foreign key

2 Answers2