MySQL – Add Unique Constraint on Combination of Two Columns

MySQL

I'm trying to make two columns (combined) unique in a mysql table. I've searched the stack, but the solutions don't work.
So I'll explain by using an example

cartID   productID
10       7           -> allowed
10       8           -> allowed
11       7           -> allowed
11       7           -> not allowed, the combination of 11 7 already exists in table

The 'solutions' I found are the following:
ALTER TABLE cart
ADD CONSTRAINT uq_cart UNIQUE(cartID, productID);

But this just seems to add a constraint on each column individual (so a 10 8 would also throw an exception if 10 7 already existed).

Best Answer

A constraint like:

UNIQUE(cartID, productID)

means that the combination of cartID and productID is unique, not the individual columns. It is however possible that something in your syntax makes MySQL ignore the constraint. Can you try the following:

CREATE TABLE Orders
( cartID int not null
, productID int not null
, unique (cartID, productID) );

insert into Orders (cartID, productID) values (10, 7), (10,8), (11,7),(11,7);
ERROR 1062 (23000): Duplicate entry '11-7' for key 'cartID'

insert into Orders (cartID, productID) values (10, 7), (10,8), (11,7);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0
Related Question