In mysql, key, primary key, unique key and index are different, mysqlunique

In mysql, key, primary key, unique key and index are different, mysqlunique

Indexes in mysql are very important knowledge points. Compared with other knowledge points, indexes are more difficult to master, and there are many index types in mysql, such as primary key, unique key, and index, this article introduces the differences between key, primary key, unique key, and index in mysql.

I. Differences between key and primary key

CREATE TABLE wh_logrecord ( logrecord_id int(11) NOT NULL auto_increment, user_name varchar(100) default NULL, operation_time datetime default NULL, logrecord_operation varchar(100) default NULL, PRIMARY KEY (logrecord_id), KEY wh_logrecord_user_name (user_name) ) 

Resolution:

KEY wh_logrecord_user_name (user_name)

Create a foreign key for the user_name field of the table and the user_name field of the wh_logrecord_user_name table
The external brackets are the corresponding tables for creating foreign keys and the corresponding fields in the brackets.
Similar to KEY user (userid)
Of course, keys may not all be foreign keys

Summary:

Key is an index constraint. fields in a table are indexed by using primary foreign unique. Foreign keys are commonly used for foreign key Association.

KEY forum (status, type, displayorder) # is a multi-column index (KEY)
KEY tid # is a single column index (KEY ).

For example, when creating a table: KEY forum (status, type, displayorder)

Select * from table group by status, type, displayorder whether this index is automatically used,

Is this index useful when select * from table group by status?

Key Usage: it is mainly used to accelerate the query speed.

Ii. Differences between KEY and INDEX

Note: I am still confused about this part.
KEY is usually the synonym of INDEX. If the primary key is specified in the column definition, the primary key can only be specified as the KEY. This is intended to be compatible with other database systems. Primary key is a unique KEY. In this case, all KEY columns must be defined as not null. If these columns are NOT explicitly defined as not null, MySQL should implicitly define these columns. A table has only one primary key.

Differences between Index and Key in MySQL

A Key is a part of the relational model theory, such as a Primary Key or a Foreign Key, used for data integrity check and uniqueness constraints. The Index is at the implementation level. For example, you can create an Index on any column of the table. When the column to be indexed is in the Where condition of the SQL statement, you can quickly locate the data, to quickly search. As for Unique Index, it only belongs to one type of Index. If Unique Index is set up, the data in this column cannot be duplicated. I guess MySQL can further optimize the Unique Index type.

Therefore, when designing a table, the Key is only at the model level. When you need to optimize the query, you can create an index for the relevant columns.

In addition, for a Primary Key column in MySQL, MySQL has automatically created a Unique Index for it without having to repeat the Index.

An explanation is as follows:

Note that "primary" is called primary key not INDEX.
KEY is something on the logical level, describes your table and database design (I. e. enforces referential integrity ...)
INDEX is something on the physical level, helps improve access time for table operations.
Behind every PK there is (usually) unique index created (automatically ).

Iii. What are the differences between unique key and primary key in mysql?

1. One or more columns of the Primary key must be not null. If the column is NULL, the column is automatically changed to not null when the primary key is added. The unique key does not have this requirement on columns.

2. A table can have only one primary key, but multiple UNIQUE keys.

3. The primary key and unique key constraints are implemented by reference to the index. If the inserted value is NULL, the full NULL value is not recorded in the index according to the index principle, therefore, duplicate values can be inserted when all NULL values are inserted, while others cannot insert duplicate values.

Alter table t add constraint uk_t_1 unique (a, B); insert into t (a, B) values (null, 1); # duplicate insert into t (a, B) values (null, null); # repeated

4. Use UNIQUE KEY

CREATE TABLE `secure_vulnerability_warning` (  `id` int(10) NOT NULL auto_increment,  `date` date NOT NULL,  `type` varchar(100) NOT NULL,  `sub_type` varchar(100) NOT NULL,  `domain_name` varchar(128) NOT NULL,  `url` text NOT NULL,  `parameters` text NOT NULL,  `hash` varchar(100) NOT NULL,  `deal` int(1) NOT NULL,  `deal_date` date default NULL,  `remark` text,  `last_push_time` datetime default NULL,  `push_times` int(11) 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=InnoDB DEFAULT CHARSET=utf8 

Unique key is mainly used to prevent duplication during data insertion.

1. When creating a table

CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), UNIQUE (Id_P) ) 

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

CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) ) 

2. When a table has been created, use the following SQL statement to create a UNIQUE constraint in the "Id_P" column:

Alter table Persons
Add unique (Id_P)

To name the UNIQUE constraint and define the UNIQUE constraint for multiple columns, use the following SQL Syntax:

Alter table Persons
Add constraint uc_PersonID UNIQUE (Id_P, LastName)

3. revoke the UNIQUE constraint.

To revoke the UNIQUE constraint, use the following SQL statement:
MySQL:

Alter table Persons
Drop index uc_PersonID

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!