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

Question

I have a table:

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

Now I want to make the columns user, email, address unique (together).

How do I do this in MySql?

Of course the example is just... an example. So please don't worry about the semantics.

Answer 1

I have a MySQL table:

CREATE TABLE `content_html` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `id_box_elements` int(11) DEFAULT NULL,   `id_router` int(11) DEFAULT NULL,   `content` mediumtext COLLATE utf8_czech_ci NOT NULL,   PRIMARY KEY (`id`),   UNIQUE KEY `my_uniq_id` (`id_box_elements`,`id_router`) ); 

and the UNIQUE KEY works just as expected, it allows multiple NULL rows of id_box_elements and id_router.

I am running MySQL 5.1.42, so probably there was some update on the issue discussed above. Fortunately it works and hopefully it will stay that way.

Answer 2

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