Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

see this table:

    CREATE TABLE IF NOT EXISTS `default_messages_users` (
  `message_id` int(11) NOT NULL,
  `owner_user_id` int(11) NOT NULL,
  `to_user_id` mediumint(8) NOT NULL
  PRIMARY KEY (`id`)

I need to set only one possible combination of the columns message_id, owner_user_id, to_user_id meaning the combination (for example) 1,1,1 only can exists one time so no duplicates is allowed. How I do this? Creating an index between them?

Cheers and thanks in advance

share|improve this question
up vote 5 down vote accepted

A UNIQUE constraint (or a unique index, mysql does not distinguish much between the two concepts of constraint and index) can be composed from more than one columns (2, 3, ...), as needed. Example for your case with a constraint made from 3 columns:

ALTER TABLE default_messages_users 
  ADD CONSTRAINT Unique__message__owner_user__to_user   --- you choose a sensible name
                                                        --- for the constraint
    UNIQUE message_id_owner_user_id_to_user_id_UQ       --- and the index 
      (message_id, owner_user_id, to_user_id) ;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.