mysql multi-field unique constraint

MySQL unique constraint (Unique Key) requires that the column be unique and is allowed to be null, but only one null value can appear. A unique constraint ensures that there are no duplicate values ​​in one or more columns.

Set the unique constraint when creating the table

Use the UNIQUE keyword directly after defining the column to specify the unique constraint, syntax rules As follows:

<字段名> <数据类型> UNIQUE

Create the data table tb_dept2, specify the unique name of the department, and enter the SQL statement and running 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 there can only be one PRIMARY KEY declaration; columns declared as PRIMAY KEY are not allowed to have null values, but the declaration NULL values ​​are allowed for UNIQUE fields.

Add a unique constraint when modifying the table

The syntax format of adding a unique constraint when modifying the table is:

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

Modify the data table tb_dept1, specify The name of the department is unique. The input SQL statement and running results are as follows.

mysql> ALTER TABLE tb_dept1
    -> ADD CONSTRAINT unique_name UNIQUE(name);
Query OK, 0 rows affected (0.63 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> 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 requirement for users to like comments. The database design is three tables, the user table t_user, the comment table t_comment, and the likes table t_praise. There are two foreign keys in the likes table: user_id and comment_id are respectively 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 to achieve this is to query through user_id and comment_id before inserting into the like table. Whether there is a like record, if not, perform the insertion operation again, otherwise it will return that you have already liked it. 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 to be unique Constraint, that is, the two columns cannot be the same at the same time. In this way, if you have already clicked a like during the insertion operation, the database will throw a violation of the unique key constraint. In this case, you can avoid one more database query operation. There are many specific settings The statement listed as a unique constraint is:

UNIQUE KEY <唯一约束名>(<列名>,...,<列名n>)

The above is the detailed content of mysql multi-field unique constraint. For more information, please follow other related articles on the PHP Chinese website!