The difference between key, primary key, unique key and index in MySQL

Transferred from: http://zccst.iteye.com/blog/1697043

one, key and primary key difference

CREATE TABLEWh_logrecord (logrecord_idint( One) not NULLAuto_increment,user_name varchar( -)default NULL, Operation_timedatetime default NULL, Logrecord_operationvarchar( -)default NULL, PRIMARY KEY(logrecord_id),KEYWh_logrecord_user_name (user_name) ) 

Analytical:

KEY wh_logrecord_user_name (user_name

The user_name field in this table establishes a foreign key with the wh_logrecord_user_name table user_name Field
Outside the parentheses is the table that establishes the foreign key, the corresponding field in parentheses
Similar to the KEY user (userid)
Of course, key may not all be foreign keys

Summarize:
Key is an index constraint that constrains the index of a field in a table by primary foreign unique. Common with foreign key, foreign key association. Used to speed up queries.

KEY Forum (status,type,displayorder)   KEY tid (TID)                         

If you are building a table:

KEYSelect*fromtableGroup by   Select*fromtableGroup by status is this index useful? The answer is auto-indexed.

Key uses: Mainly to speed up the query.

second, key and index difference

Key is usually the index synonym. If the keyword attribute PRIMARY key is given in the column definition, then PRIMARY key can also be specified as key only. The purpose of this is to be compatible with other database systems . PRIMARY key is a unique key, at which point all the keyword columns must be defined as not NULL. These columns should be implicitly defined if these columns are not explicitly defined as not null,mysql. A table has only one primary KEY.

The difference between index and key in MySQL:
Key is a part of the relational model theory, such as a primary key (Primary key), a foreign key (Foreign key), etc., for data integrity checking and uniqueness constraints. Index is at the implementation level, such as the table can be indexed to any column, then when the indexed column in the SQL statement in the where condition, you can get fast data positioning, so as to quickly retrieve . As for the unique index, it is only one of the index, the creation of a unique index indicates that this column of data is not repeatable , guess MySQL index of unique index type can be further special optimization bar.

Thus, when designing a table, key is only at the model level, and when query optimization is required, the related columns are indexed.

Also, in MySQL, for a column of primary key, MySQL has automatically created a unique index on it, without having to re-index it.

A section of the search explanation:

Note that "Primary” isCalledPRIMARY KEY  not INDEX. KEY  isSomething onThe logical Level, describes yourTable  and Databasedesign (i.e. enforces referential integrity ...) INDEX  isSomething onThe physical Level, helps improve access time for Tableoperations. Behind every PK there is(usually)Unique IndexCreated (automatically).

Iii. What is the difference between a unique key and primary key in MySQL

The 1 or more columns of the

1,primary key must be NOT null and, if listed as NULL, the column is automatically changed to NOT NULL when the Primary key is added. The unique key does not have this requirement for the column  

2, a table can have only one primary KEY, but there may be more than one unique key 

3, the primary key and the unique key constraint are implemented through the reference index, If the inserted value is NULL, the null value is not recorded on the index according to the principle of the index, so when you insert a full null value, you can have duplicates, while others cannot insert duplicate values.  

Alter Table Add constraint Unique  Insertintovalues (null,1);     Insert  into Values (null,null

Iv. use of unique KEY

CREATE TABLE' secure_vulnerability_warning ' (' ID ')int(Ten) not NULLauto_increment, ' Date ' date not NULL, ' type 'varchar( -) not NULL, ' Sub_type 'varchar( -) not NULL, ' domain_name 'varchar( -) not NULL, ' url 'text  not NULL, ' Parameters 'text  not NULL, ' hash 'varchar( -) not NULL, ' deal 'int(1) not NULL, ' deal_date ' datedefault NULL, ' remark 'text, ' Last_push_time 'datetime default NULL, ' Push_times 'int( One)default '1', ' First_set_ok_time 'datetime default NULL, ' Last_set_ok_time 'datetime default NULL,   PRIMARY KEY(' id '),UNIQUE KEY' Date ' (' Date ', ' hash ') ENGINE=InnoDBDEFAULTCHARSET=Utf8

The use of UNIQUE key is mainly used to prevent duplication of data when it is inserted.

1, when creating a table

CREATE TABLEPersons (id_pint  not NULL, LastNamevarchar(255) not NULL, FirstNamevarchar(255), Addressvarchar(255), Cityvarchar(255), UNIQUE(id_p))

If you need to name a unique constraint and define a UNIQUE constraint for multiple columns, use the following SQL syntax:

CREATE TABLEPersons (id_pint  not NULL, LastNamevarchar(255) not NULL, FirstNamevarchar(255), Addressvarchar(255), Cityvarchar(255), CONSTRAINTUc_personidUNIQUE(id_p,lastname))

2, when the table has been created, to create a UNIQUE constraint in the "id_p" column, use the following SQL:

ALTER TABLE  ADDUNIQUE

To name a unique constraint and define a UNIQUE constraint for multiple columns, use the following SQL syntax:

ALTER TABLE  ADDCONSTRAINTUNIQUE

3, REVOKE UNIQUE constraint

To revoke a UNIQUE constraint, use the following SQL:
Mysql:

ALTER TABLE  DROP INDEX 

The difference between key, primary key, unique key and index in MySQL