Analysis of the difference between unique and primary constraints in MySQL

A field that defines a unique constraint cannot contain duplicate values, you can define a unique constraint for one or more fields, so unique can be defined at the field level or at the table level, and the field on a unique constraint can contain a null value. Oracle automatically establishes a unique index and a NOT NULL constraint for fields with PRIMARY key constraints, which can be indexed for primary KEY constraints, uniqued nullable, and can be defined in one or more fields in a table;

PRIMARY key cannot be repeated, in a table can define a joint primary key; Simply put, PRIMARY key = unique + NOT NULL

Unique is unique when you need to qualify one of your table fields for each value that is unique and has no duplicate values. For example, if you have a person_info table and you have an ID column in the table, you can specify that the field is unique.

From a technical point of view, there are many similarities between the Primary key and the unique key. But there are the following similarities and differences:

Same: They all belong to entity integrity constraints.

Cases

Table structure:

The code is as follows	Copy Code
CREATE TABLE ' good_booked ' ( ' auto_id ' int (a) not NULL auto_increment, ' good_id ' int (one) default NULL, ' chemist_id ' int (one) 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 a unique key for a field in a table:

The code is as follows	Copy Code
ALTER TABLE good_booked DROP INDEX good_id;

Unique prevent repeat Insert Data sample

When the unique column inserts a record containing duplicate values on a unique key, we can control how MySQL handles this: Skip inserts, break operations, or update old records with the Ignore keyword or onduplicatekeyupdate clause. This prevents duplicate data items from being present in the database

The code is as follows

Copy Code

mysql> Createtablemenus (Idtinyint (4) notnullauto_increment, ->labelvarchar (a) Null,urlvarchar ( ID)); queryok,0rowsaffected (0.13sec) Mysql>insertintomenus (Label,url) VALUES ("Home", "home.html"); Queryok,1rowaffected (0.06sec) Mysql>insertintomenus (Label,url) VALUES ("AboutUs", "aboutus.html"); Queryok,1rowaffected (0.05sec) Mysql>insertintomenus (Label,url) VALUES ("Services", "services.html"); Queryok,1rowaffected (0.05sec) Mysql>insertintomenus (Label,url) VALUES ("Feedback", "feedback.html"); queryok,1rowaffected (0.05sec) Mysql>select*frommenus +--+ ———-+ ————— + |id|label|url| +--+ ——— -+ ————— + |1| home|home.html| |2| aboutus|aboutus.html| |3| services|services.html| |4| feedback|feedback.html| +--+ ———-+ ————— + 4rowsinset (0.00sec)

If you now insert a record that violates a unique constraint in the unique column, MySQL interrupts the operation and prompts for an error:

The code is as follows	Copy Code
Mysql>insertintomenus (Id,label,url) VALUES (4, "ContactUs", "contactus.html"); ERROR1062 (23000):D uplicateentry "4″forkey" id

When you add the Ignore keyword to the previous INSERT statement, if you think that the statement violates the unique constraint, MySQL does not even attempt to execute the statement, so the following statement does not return an error:

The code is as follows	Copy Code
Mysql>insertignoreintomenus (Id,label,url) VALUES (4, "ContactUs", "contactus.html"); Queryok,0rowsaffected (0.00SEC) mysql>select*frommenus; +--+ ———-+ ————— + |id|label|url| +--+ ———-+ ————— + |1| home|home.html| |2| aboutus|aboutus.html| |3| services|services.html| |4| feedback|feedback.html| +--+ ———-+ ————— + 4rowsinset (0.00SEC)

When there are a lot of INSERT statements that need to be executed sequentially, the Ignore keyword makes the operation easier. Use it to ensure that MySQL skips over it (rather than discarding it all), regardless of which insert contains duplicate key values.

In this case, we can also allow MySQL to automatically convert the insert operation to an update operation by adding the MySQL4.1 new onduplicatekeyupdate clause. This clause must have a list of fields that need to be updated, and the list 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, "ContactUs", "contactus.html") ->onduplicatekeyupdatelabel= "ContactUs", url= "contactus.html"; Queryok,2rowsaffected (0.05SEC)

In this case, if the MySQL discovery table already contains a record with the same unique key, it automatically updates the old record as 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| +--+ ———— + —————-+ 4rowsinset (0.01SEC)

Different points:

(1) The column containing the uniqueness constraint allows null values, but the column that contains the PRIMARY KEY constraint does not allow null values.

(2) A Uniqueness constraint can be placed on one or more columns, and the combination of these columns or columns must have unique. However, the column that contains the uniqueness constraint is not the primary key column of the table.

(3) A Uniqueness constraint forces a unique index to be created on the specified column. By default, nonclustered indexes of uniqueness are created, but you can also specify that the index being created is a clustered index.

(4) The purpose of establishing a primary key is to have the foreign key refer to it.

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