Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

share|improve this question
    
do you mean that if 1,2,3 is stored, 3,2,1 is not allowed? –  Emmad Kareem Aug 23 '12 at 13:14
    
@EmmadKareem no I mean 1,2,3 is stored and then 1,2,3 can't be added again. I think to set three fields as KEYS is that correct? –  ReynierPM Aug 23 '12 at 13:16
add comment

1 Answer 1

up vote 3 down vote accepted
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
    
Thanks this is what I was looking for –  ReynierPM Aug 23 '12 at 13:39
    
I'm not sure why you would need such a Unique key though. Are you sure you don't need one on (owner_user_id, to_user_id)? –  ypercube Aug 23 '12 at 14:39
    
Well I need this unique key because the same message can only be send one time to the same person so if I send message_id = 1 from owner_user_id = 1 and to_user_id = 2 then I can not send the send message once again so 1,1,2 and 1,1,2 can't exists but 1,1,3 or 1,2,1 or 1,2,3 can exists, understand my point? I'm right? Maybe not ;) –  ReynierPM Aug 23 '12 at 14:45
    
Yes, it makes sense that way. Without the overall table design, I can't comment more. –  ypercube Aug 23 '12 at 14:47
    
Another thing that confuses me is why owner_user_id is int while to_user_id is mediumint. I would expect them to be of the same datatype. –  ypercube Aug 23 '12 at 14:49
show 1 more comment

Your Answer

 
discard

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.