what is mysql unique

In mysql, unique refers to "unique constraint", which means that the values ​​of fields in all records cannot appear repeatedly. There can be multiple unique constraints in a table, and the column where the unique constraint is set is allowed to have null values, but there can only be one null value. The syntax for adding a unique constraint when modifying a table is "ALTER TABLE ADD CONSTRAINT UNIQUE();".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, unique refers to "unique constraint".

MySQL unique constraint (UNIQUE KEY)

MySQL unique constraint (Unique Key) means that the values ​​of the fields in all records cannot Repeatedly. For example, after adding a unique constraint to the id field, the id value of each record is unique and cannot be repeated. If the id value of one of the records is '0001', then there cannot be another record with the id value of '0001' in the table.

Unique constraints are similar to primary key constraints in that they can ensure the uniqueness of columns. The difference is that there can be multiple unique constraints in a table, and the column where the unique constraint is set is allowed to have null values, but there can only be one null value. There can only be one primary key constraint in a table, and no null values ​​are allowed. For example, in the user information table, in order to avoid duplicate user names in the table, the user name can be set as a unique constraint.

Set unique constraints when creating the table

Unique constraints can be set directly when creating the table, usually on other columns except the primary key.

Use the UNIQUE keyword directly after defining the column to specify unique constraints. The syntax format is as follows:

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

Example 1:

Create the data table tb_dept2 and specify the unique name of the department , the SQL statement and running results are as follows.

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)

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(<列名>);

Example 2:

Modify the data table tb_dept1 and specify a unique department name. The 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)

Delete a unique constraint

The syntax format for deleting a unique constraint in MySQL is as follows:

ALTER TABLE <表名> DROP INDEX <唯一约束名>;

Example 3:

Delete The unique constraint unique_name in the data table tb_dept1, the SQL statement and the running results are as follows.

mysql> ALTER TABLE tb_dept1
    -> DROP INDEX unique_name;
Query OK, 0 rows affected (0.20 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   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

[Related recommendations: mysql video tutorial]

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