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

Question

Example:

category : food, bike, car

Items under the category:

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

here, the categories "bike" and "car" may contain the same items as the "Honda". But no category can contain the same elements.

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 it item_idwill be unique with the same category_id, you need to know the procedure in MySql.

Answer 1

You will need a unique key (or primary key) in a combination of category_id and item_id:

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

Answer 2

. category_id item_id

Answer 3

.

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 a table with many: many relationships.

Just a coincidence is that two elements in different categories have the same name. Just like my name Rick James(category super geeks), and there was a singer by name Rick James(category super freaks). It is not necessary that both of our names coexist on the same row in any table.