How to add unique constraint in mysql?

Method: 1. When creating a table, use the "CREATE TABLE table name (field name data type UNIQUE);" statement to add; 2. When modifying the table, use "ALTER TABLE table name ADD CONSTRAINT unique constraint name UNIQUE (column name);" statement to add.

(Recommended tutorial: mysql video tutorial)

MySQL unique constraint (Unique Key) refers to the The value of the field 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.

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 superior.

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

CREATE TABLE <数据表名>(<字段名> <数据类型> UNIQUE);

Example 1

Create data table tb_dept2, The name of the specified department is unique. 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 for 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)

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