1

Is it possible, in MySQL, to create two unique indexes, together?

For example, there is a table that holds two IDs - customer ID and product ID. I would like to create uniqueness for the pair only. In simple words, combination of user and product can be present only once. The same user with another products can be present as much as needed. And the same product with another users can be present as much as needed. But the pair itself to be unique. How?

This query actually has an OR relation. I am looking for an AND relation.

ALTER TABLE top ADD CONSTRAINT unique_pair UNIQUE (uid, pid)
Siguza
  • 21,155
  • 6
  • 52
  • 89
Dinly
  • 161
  • 2
  • 10
  • 1
    Have a look at this. Is this you want to achieve? http://stackoverflow.com/questions/21991367/using-group-of-columns-as-a-unique-key-in-mysql – Krishnadas PC Feb 21 '16 at 14:07
  • Your syntax does what you want. – Gordon Linoff Feb 21 '16 at 14:11
  • It seems that it is not. If I insert the same user with another product, it says UNIQUE and won't get inserted into the table. – Dinly Feb 21 '16 at 14:15
  • Then you have another index on your table and you need to drop it – sagi Feb 21 '16 at 14:18
  • The only index is primary key on a separate column. These two columns have no indexes. Double checked. – Dinly Feb 21 '16 at 14:24
  • Possible duplicate of [MySQL - Make a pair of values unique](http://stackoverflow.com/questions/12763726/mysql-make-a-pair-of-values-unique) – Siguza Feb 21 '16 at 14:24

1 Answers1

1

This does what you describe:

ALTER TABLE top ADD CONSTRAINT unq_top_unique_pair UNIQUE (uid, pid);

As does:

CREATE UNIQUE INDEX unq_top_uid_pid ON TOP(uid, pid);

Both of these create a unique index, which is then used for enforcing the constraint.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • It seems that it is not. If I insert the same user with another product, it says UNIQUE and won't get inserted into the table. – Dinly Feb 21 '16 at 14:15
  • @Dinly . . . Then perhaps you have *another* constraint only on `uid`. Each constraint is evaluated separated from the others. – Gordon Linoff Feb 21 '16 at 14:16