Shulou(Shulou.com)06/01 Report--

This article will explain in detail the only way to guarantee multi-fields in MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Some tables in mysql sometimes need to make unique constraints on some fields. Of course, you can also determine whether there is a unique constraint on some fields before insert to prevent repetition. If you do not want to add additional code to implement the unique constraints of some fields more flexibly, mysql provides two ways:

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 need for users to like comments, and the database is designed to have three tables, user table t_user, comment table t_comment, and comment table t_praise, in which two foreign keys are user_id and comment_id, respectively, which are associated with the user id of the user table and the comment id of the comment table, and then stipulate that a user can only like the same comment once. One way is to insert the praise table before the comment table. First use user_id and comment_id to query whether there is a like record, if not, then perform the insert operation, otherwise return that 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 praise table to be unique constraints, that is, the two columns cannot be the same at the same time, so that if you perform the insert operation, if you have already liked it, the database will throw a violation of the unique key constraint, so that you can avoid one more database query operation. The statements that specifically set multiple columns as unique constraints are:

CREATE TABLE `tpraise` (`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`) this is the end of MySQL's method of ensuring the uniqueness of multiple fields. I hope the above content can be helpful to you. You can learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.