How does MySQL ensure that multiple fields are unique?

Recommended course: MySQL tutorial

Some tables in mysql sometimes need to make unique constraints on some fields. Of course, you can also judge before inserting Is there any way to prevent duplication? If you don’t want to add additional code to more flexibly implement unique constraints on some fields, mysql provides two methods:

1.unique key

alter table xx

add unique key no_account(no,collection_account)

2.unique index

alter table xxadd unique no_account_index(no,collection_account);

Suppose there is a requirement for users to like comments. The database design is three tables, user table t_user, comment table t_comment, like table t_praise, among which likes There are two foreign keys in the table, user_id and comment_id, which are respectively associated with the user id of the user table and the comment id of the comment table. Then it is stipulated that a user can only like the same comment once. One way to achieve this is to insert the like. Before entering the table, first query whether there are like records through user_id and comment_id. If not, perform the insertion operation, otherwise it will return the likes you have already liked. If implemented in this way, there will be one more database query operation. A better implementation is to modify The user_id and comment_id of the like table are unique constraints, that is, these two columns cannot be the same at the same time. In this way, if the insert operation is performed, if the like has already been liked, the database will throw a violation of the unique key constraint. In this way, you can avoid multiple A database query operation was performed. The specific statement to set the unique constraints on multiple columns is:

CREATE TABLE `t_praise` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` int(12) NOT NULL,
  `user_id` int(12) NOT NULL,
  KEY `FK_t_praise_comment` (`comment_id`),
  KEY `FK_t_praise_user` (`user_id`),
  UNIQUE KEY `UK_praise` (`comment_id`,`user_id`)

)

The above is the detailed content of How does MySQL ensure that multiple fields are unique?. For more information, please follow other related articles on the PHP Chinese website!