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

 

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. 
    • ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`);

    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 `id_box_elements` (`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.

    Multi column unique indexes do not work in MySQL if you have a NULL value in row as MySQL treats NULL as a unique value and at least currently has no logic to work around it in multi-column indexes. Yes the behavior is insane, because it limits a lot of legitimate applications of multi-column indexes, but it is what it is... As of yet, it is a bug that has been stamped with "will not fix" on the MySQL bug-track...

    链接地址: http://www.djcxy.com/p/93682.html

    上一篇: PHP：是mysql

    下一篇: 我如何为MySQL中的多列指定唯一约束？