CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Shafayet Hossen
Shafayet Hossen

Reputation: 336

How to handle unique items under one category in MySql, where items may not be unique under different category?

Example: 

category : food, bike, car

Items under category: 

food = {rice, fish, beef};
bike = {Honda, Suzuki};
car = {Honda, Toyota, BMW};

here, 'bike' and 'car' different category can hold same items like 'Honda'. But neither category can hold same items.

Table name (column name): 

1. category
-> id (primary, auto increment)
-> category_name (char, unique)

2. items
-> id (primary, auto increment)
-> item_name (char)

3. category_item_relation (many to many relation between table 1 and 2)
-> category_id (foreign key)
-> item_id (foreign key)

where item_id will be unique under same category_id, need to know the procedure in MySql.

Upvotes: 4

Views: 63

Answers (3)

Rick James
Rick James

Reputation: 142298

I disagree with the two preceding answers.

CREATE TABLE Categories (
    cat VARCHAR(...) NOT NULL,
    ...
    PRIMARY KEY name
);

CREATE TABLE Items (
    cat VARCHAR(...) NOT NULL,  -- see Categories.cat
    item VARCHAR(...) NOT NULL,
    ...
    PRIMARY KEY(cat, item),
    INDEX(item)   -- possibly needed
);

There is no need for the many:many relation table.

It is just coincidence that two items in different categories have the same name. Just like my name is Rick James (of category super geeks) and there was a singer named Rick James (of category super freaks). There is no need for both of our names to coexist in a single row in any table.

Upvotes: 0

Bokul
Bokul

Reputation: 395

In that case you have to use Candidate Key. So you will have to create a unique key on combination of category_id and item_id

Upvotes: 2

Mureinik
Mureinik

Reputation: 311348

You need a unique key (or primary key) on the combination of category_id and item_id:

ALTER TABLE category_item_relation
ADD CONSTRAINT item_category_relation_pk
PRIMARY KEY (category_id, item_id)

Upvotes: 3

Related Questions