How to delete unique key in mysql

In mysql, you can use the "ALTER TABLE table name DROP INDEX unique key name" statement to delete a unique key; the ALTER TABLE statement is used to add, delete or modify data, and the DROP INDEX statement is used to express Delete constraint operation.

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

How to delete unique key in mysql

MySQL unique constraint (Unique Key) means that the value of the field in all records cannot be repeated.

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.

Delete a unique constraint

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

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

The example is as follows:

Delete data The unique constraint unique_name in table tb_dept1, the SQL statement and 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)

Extended knowledge:

Set the unique constraint when creating the table

The unique constraint can be set directly when creating the table, usually in addition to the primary key on other columns.

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

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

Example 1

Create the data table tb_dept2 and specify the name of the department to be unique. The SQL statement and running results are 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)

Recommended learning: mysql video tutorial

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