In my project I have product_details, categories, departments table.
product_details belongs to categories and departments table and categories has many product_details and departments has many product_details.
Am trying to insert product details using nodejs. I have structured product_name as unique to avoid redundancy. If product with same belongs to different category or departments it is throwing an error and it is not saving those product details. But I want to store the product details with same which belongs to different category or different department. How to do this. If I remove unique constraint then redundancy occurs.
What i want if product with name belongs to same category and same department i don't want save those details, if product with same name belongs to different category and different department i want to save those details. Please help me how achieve this.
Using the link How do I specify unique constraint for multiple columns in MySQL? given by Linus G Thiel I solve this problem.
alter table product_details add unique index (name,category_id,department_id);
It is working fine.