how to create cluster key in mysql?

Question

I have a table rooms and user. In the table user I want store room_id is the cluster key and id_user can be duplicate. Example room1 have user (1,2,3..), room2 have users (1,2,3). And name of user can be different ex: with room1 i have the id user is 1 and name is Joni, but room2 id user is 1 and name can be Joini or Sam.
But with can't duplicate when have sample the room_id and user_id. ex the room1 have user 1, it can't have a row second stored the room1 and user 1.

it's sample cassandra db

Is not clear but seems you are looking for a composite unique index .. you want that the couple id_room, id_user is unique? — ScaisEdge, Jul 31 '16 at 19:23

score 0 · Accepted Answer · answered Jul 31 '16 at 19:25

0

Could be you are looking for a composite unique index (key)

ALTER TABLE `your_table` ADD UNIQUE `room_user_index`(`id_room`, `id_user`);

answered Jul 31 '16 at 19:25

ScaisEdge

131,976
10
91
107

score 0 · Answer 2 · answered Jul 31 '16 at 20:52

If you specify both of id_rooms and id_user columns as primary key (composite primary key) then a clustered index and unique key will be created.

The order of composite keys is important for look up speed.

I suggest you study about relational dbms concepts to obtain better vision.

how to create cluster key in mysql?

2 Answers2