How to set row value to be unique in mysql?

mysql can set the row value to be unique by adding unique constraints to the row (field), the syntax format is "CREATE TABLE table name (field name data type UNIQUE);" and "ALTER TABLE table name ADD CONSTRAINT unique constraint Name UNIQUE (column name);".

(Recommended tutorial: mysql video tutorial)

MySQL database uniqueness setting unique index

In the database design, the fields that set the primary key are non-repeating and unique. If there are other fields that also need to be unique, how should they be set? For example, a table that stores user information must ensure that the mobile phone number of each record is different. At this time, you need to set uniqueness for this field.
After setting the uniqueness of the field, it is guaranteed at the database level that the field will not have the same value. Let’s talk about how to set uniqueness:

mysql command line operation index

1. Add

CREATE TABLE `member` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(18) NOT NULL ,
`phone` varchar(18) NOT NULL unique,
PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

when creating the table 2. Change

alter table member add unique(phone);

# after creating the table ##Use Navicat tool to add index

It is more convenient to use navicat to operate mysql database. It is recommended that everyone use this method.

When designing the table: Select the index as shown in the figure, the first item 'name', you can choose it yourself A meaningful name will do. The second item is the field you want to set uniqueness, and then select unique as the index type.

Select the table name to be modified, right-click to select the design table

Commonly encountered problems

If the uniqueness is added later (after the table is designed and there is already some data in the table)
There may be situations where some data in the table does not satisfy the uniqueness, for example: you want to add the field phone Set uniqueness, but currently there are two data phones in the table, both of which have the same mobile phone number. This is an error when setting uniqueness.
As shown in the picture above (this situation will also occur under command line operation, but in the form of text)
At this time, duplicate data should be deleted and then uniqueness set.

The combination of two or more fields forms a unique index

Example: A user has saved his name and mobile phone number.

Only one mobile phone number is allowed to correspond to one name. That is, the combination of name and phone cannot be repeated, that is to say, there cannot be two records with the same name and phone.
How to set it in mysql? Here we only give the method in navicat: as shown in the figure, you only need to select two fields!

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