How do I specify unique constraint for multiple columns in MySQL?

ghz 1years ago ⋅ 9512 views

Question

I have a table:

table votes (
    id,
    user,
    email,
    address,
    primary key(id),
);

How can I make the columns user, email, address unique - i.e., ensure that there isn't any pair of rows that has identical values for all three columns?

Answer

To add a unique constraint, you need to use two components:

ALTER TABLE - to change the table schema and,

ADD UNIQUE - to add the unique constraint.

You then can define your new unique key with the format 'name'('column1', 'column2'...)

So for your particular issue, you could use this command:

ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`);
#mysql