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

The main purpose of this article is to tell you briefly about the role of mysql multi-field setting unique constraints. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article can bring you some practical help. I hope that mysql multi-field setting unique constraints have any effect.

Set unique constraints when creating a table

Use the UNIQUE keyword to specify unique constraints directly after the column is defined. The syntax rules are as follows:

UNIQUE

Create the data table tb_dept2, and specify that the name of the department is unique. Enter the SQL statement and run the results as shown below.

Mysql > CREATE TABLE tb_dept2-> (- > id INT (11) PRIMARY KEY,-> name VARCHAR (22) UNIQUE,-> location VARCHAR (50)->); Query OK, 0 rows affected (0.37 sec) mysql > DESC tb_dept2 +-+ | Field | Type | Null | Key | Default | Extra | +- +-+ | id | int (11) | NO | PRI | NULL | name | varchar (40) | YES | UNI | NULL | | location | varchar (50) | YES | | NULL | | +-+ -+ 3 rows in set (0.08 sec)

Tip: the difference between UNIQUE and PRIMARY KEY: a table can have multiple fields declared as UNIQUE, but only one PRIMARY KEY declaration; columns declared as PRIMAY KEY do not allow null values, but fields declared as UNIQUE allow null values.

Add unique constraints when you modify a table

The syntax format for adding unique constraints when modifying a table is:

ALTER TABLE ADD CONSTRAINT UNIQUE ()

Modify the data table tb_dept1 to specify that the name of the department is unique. Enter the SQL statement and run the results as shown below.

Mysql > ALTER TABLE tb_dept1-> ADD CONSTRAINT unique_name UNIQUE (name); Query OK, 0 rows affected (0.63 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_dept1 +-+ | Field | Type | Null | Key | Default | Extra | +- +-+ | id | int (11) | NO | PRI | NULL | name | varchar (22) | NO | UNI | NULL | | location | varchar (50) | YES | | NULL | | +- +-+ 3 rows in set (0.00 sec)

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:

UNIQUE KEY (,...,) 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`))

Our section will capture some industry news and professional knowledge to share with you every day.

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.