Difference between unique and primary constraints in Mysql

This article will introduce the differences between unique and primary constraints in mysql. unique and primary are the types we often use when creating mysql. Next I will introduce them to you.

Fields defining the UNIQUE constraint cannot contain duplicate values. You can define a UNIQUE constraint for one or more fields. Therefore, UNIQUE can be defined at the field or table level, fields with UNIQUE constraints can contain null values. ORACLE automatically creates a unique index and a not null constraint for fields with the primary key constraint (PRIMARY code segment). When defining the primary key constraint, it can be its index; UNIQUED can be empty, one or more fields in a table can be defined;
 

Primary key cannot be empty and cannot be repeated. You can define a joint primary key in a table. In short, primary key = unique + not null

 
Unique is unique. It is used when you need to limit that each value of a table field is unique and there are no duplicate values. for example, if you have a person_Info table with a column ID card, you can specify this field unique.

 
From a technical point of view, there are many similarities between Primary Key and Unique Key. However, there are the following similarities and differences:
 

Same: they all belong to entity integrity constraints.

 
Example

Table Structure:

The Code is as follows:	Copy code
Create table 'good _ booked '( 'Auto _ id' int (10) not null auto_increment, 'Good _ id' int (11) default NULL, 'Chemist _ id' int (11) default NULL, Primary key ('Auto _ id '), Unique key 'good _ id' ('good _ id', 'chemist _ id '), KEY 'current _ state' ('current _ state '), KEY 'send _ time' ('send _ Time ') )

To delete the unique key of a field in a table:
 

The Code is as follows:	Copy code
Alter table good_booked drop index good_id;

UNIQUE example of preventing repeated data insertion

When a unique column inserts records containing duplicate values on a UNIQUE key, we can control how MySQL handles this situation: use the IGNORE keyword or the ONDUPLICATEKEYUPDATE clause to skip the INSERT operation, interrupt the operation, or update the old record as the new value. This prevents duplicate data items in the database.

The Code is as follows:

Copy code

Mysql> createtablemenus (idtinyint (4) notnullauto_increment, -> Labelvarchar (10) null, urlvarchar (20) null, uniquekey (id )); QueryOK, 0 rowsaffected (0.13sec) Mysql> insertintomenus(label,url?values({home},”home.html "); QueryOK, 1 rowaffected (0.06sec) Mysql> insertintomenus(label,url?values(“aboutus},”aboutus.html "); QueryOK, 1 rowaffected (0.05sec) Mysql> insertintomenus(label,url1_values(“services”,”services.html "); QueryOK, 1 rowaffected (0.05sec) Mysql> insertintomenus(label,url?values({feedback},”feedback.html "); QueryOK, 1 rowaffected (0.05sec) Mysql> select * frommenus; + -- + ---- + ----- + | Id | label | url | + -- + ---- + ----- + | 1 | Home | home.html | | 2 | Aboutus | aboutus.html | | 3 | Services | services.html | | 4 | Feedback | feedback.html | + -- + ---- + ----- + 4 rowsinset (0.00sec)

If you insert a record in the unique column that violates the unique constraint, MySQL will interrupt the operation and prompt an error:

The Code is as follows:	Copy code
Mysql> insertintomenus(id,label,url?values(4,20.contactus},%contactus.html "); ERROR1062 (23000): Duplicateentry "4" forkey "id"

When the IGNORE keyword is added to the previous INSERT statement, if the statement violates the unique constraint, MySQL will not even try to execute this statement. Therefore, the following statement will not return an error:

The Code is as follows:	Copy code
Mysql> insertignore1_menus(id,label,url?values(4,20.contactus},#contactus.html "); QueryOK, 0 rowsaffected (0.00sec) Mysql> select * frommenus; + -- + ---- + ----- + | Id | label | url | + -- + ---- + ----- + | 1 | Home | home.html | | 2 | Aboutus | aboutus.html | | 3 | Services | services.html | | 4 | Feedback | feedback.html | + -- + ---- + ----- + 4 rowsinset (0.00sec)

When many INSERT statements need to be executed sequentially, the IGNORE keyword makes the operation very convenient. Using it ensures that MySQL skips an INSERT regardless of which one contains duplicate key values (instead of dropping all operations ).

In this case, we can also add the ONDUPLICATEKEYUPDATE clause added by MySQL4.1 to enable MySQL to automatically convert the INSERT operation to the UPDATE operation. This clause must have a list of fields to be updated, which is the same as the list used by the UPDATE statement.

The Code is as follows:	Copy code
Mysql> insertintomenus(id,label,url?values(4,20.contactus},%contactus.html ") -> Onduplicatekeyupdatelabel=contactus”, url?contactus.html "; QueryOK, 2 rowsaffected (0.05sec)

In this case, if MySQL finds that the table already contains records with the same unique key, it will automatically update the old record to the new value specified in the ONDUPLICATEKEYUPDATE clause:

The Code is as follows:	Copy code
Mysql> select * frommenus; + -- + ---- + ------ + | Id | label | url | + -- + ---- + ------ + | 1 | Home | home.html | | 2 | Aboutus | aboutus.html | | 3 | Services | services.html | | 4 | Contactus | contactus.html | + -- + ---- + ------ + 4 rowsinset (0.01sec)

Differences:
 

(1) The column where the uniqueness constraint is located allows null values, but the column where the primary key constraint is located does not allow null values.

(2) You can place the uniqueness constraint on one or more columns. The combination of these columns or columns must be unique. However, the column where the uniqueness constraint is located is not the primary key column of the table.

 

(3) The uniqueness constraint forces the creation of a unique index on the specified column. By default, a unique non-clustered index is created. However, you can also specify that the created index is a clustered index.

(4) The primary key is created so that the foreign key can be referenced.

(5) A table can have at most one primary key, but it can have many unique keys.