2

see this table:

    CREATE TABLE IF NOT EXISTS `default_messages_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_id` int(11) NOT NULL,
  `owner_user_id` int(11) NOT NULL,
  `to_user_id` mediumint(8) NOT NULL
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

0

1 Answer 1

6

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) ;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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