How to delete unique key (unique constraint) in mysql

Two methods to delete a unique key (unique constraint): 1. Use the DROP INDEX statement to delete the specified unique constraint from the specified table, the syntax is "DROP INDEX unique constraint name ON table name;". 2. Use the ALTER TABLE statement to delete the specified unique constraint from the specified table. The syntax is "ALTER TABLE table name DROP INDEX unique constraint name;".

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

MySQL unique constraint (Unique Key) means that the values ​​of the fields in all records cannot appear 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.

Two methods for mysql to delete unique key (unique constraint)

Method 1. Use the DROP INDEX statement to delete

DROP INDEX 唯一约束名 ON 表名;

Example:

Create the data table tb_dept, specify the unique name of the department

CREATE TABLE tb_dept(
   id INT(11) PRIMARY KEY,
   name VARCHAR(22),
   location VARCHAR(50),
   CONSTRAINT unique_name UNIQUE(name)
);

##View the table results

DESC tb_dept;

Delete the unique constraint unique_name in the data table tb_dept

DROP INDEX unique_name ON tb_dept;

Method 2. Use ALTER TABLE Statement delete

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

Example:

In the data table tb_dept, specify the location field to be unique, and set the unique constraint name unique_name

ALTER TABLE tb_dept ADD CONSTRAINT unique_name UNIQUE(location);

Delete unique_name constraint

[Related recommendations:

mysql video tutorial]

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