Detailed explanation of the differences between KEY and PRIMARY KEY and UNIQUE KEY and INDEX in MySQL

This article will share with you the differences between KEY, PRIMARY KEY, UNIQUE KEY and INDEX in mysql index, that is, the difference between primary key index, unique index and ordinary index. I hope you can like it

For the question The problems raised can be broken down and solved step by step. KEY and INDEX are synonymous in MySQL. Then this problem can be simplified to the difference between PRIMARY KEY, UNIQUE KEY and INDEX. And these three are also the divisions of indexes, primary key index, unique index and ordinary index (INDEX).

Use INDEX to speed up reading data from the database. INDEX is usually added to those columns in JOIN, WHERE, and ORDER BY clauses.

When creating an index, you need to ensure that the index is a condition applied to the SQL query statement (usually as a condition of the WHERE clause). In fact, the index is also a table that saves the primary key and index fields and points to the records of the entity table.

Indexes also have their disadvantages: although indexes improve query speed, they will reduce the speed of updating the table, such as INSERT, UPDATE and DELETE on the table. Because when updating the table, MySQL not only needs to save the data, but also save the index file.

The difference between KEY and INDEX in MySQL

KEY is usually a synonym for INDEX. PRIMARY KEY can also be specified as KEY only if the keyword attribute PRIMARY KEY is given in the column definition. This is done for compatibility with other database systems. PRIMARY KEY is a unique KEY. At this time, all key columns must be defined as NOT NULL. If these columns are not explicitly defined as NOT NULL, MySQL should define these columns implicitly.

KEY is a key value, which is part of the relational model theory, such as primary key (PRIMARY KEY), foreign key (Foreign KEY), etc., which are used for data integrity checking and uniqueness constraints. INDEX is at the implementation level. For example, you can create an index on any column of a table. Then when the indexed column is in the Where condition in the SQL statement, you can get fast data location and thus fast retrieval. As for UNIQUE INDEX, it is just one type of INDEX. The establishment of UNIQUE INDEX means that the data in this column cannot be repeated. I guess MySQL can further optimize the UNIQUE INDEX type index.

So, when designing the table, the KEY only needs to be at the model level, and when query optimization is required, indexes on the relevant columns can be created.

KEY

KEY is the physical structure of the database, which contains two levels of meaning. One is constraints, which focuses on constraining and standardizing the structural integrity of the database, and the other is index. Auxiliary query.
•Primary key has two functions. One is constraint, which is used to standardize a storage primary key and uniqueness, but at the same time it also creates an index on this key;
•Unique key also has two functions Functions, one is constraint, which regulates the uniqueness of data, but at the same time it also establishes an index on this key;
•foreign key also has two functions, one is constraint, which regulates the data Reference integrity, but an index is also established on this key;

It can be seen that the key has the meaning of both constraint and index.

INDEX

INDEX is also the physical structure of the database, but it only serves as an auxiliary query. It will occupy additional space when it is created. Indexes are divided into prefix indexes, full-text indexes, etc. An index is just an index and does not constrain the behavior of indexed fields.

The difference between PRIMARY KEY and UNIQUE KEY

PRIMARY KEYs (primary key) and UNIQUE KEYs (unique key constraints) are similar. PRIMARY KEY is usually a column, but it is also possible Multiple columns, usually determine a row of data (row). A table can have only one PRIMARY KEY, but can have many UNIQUE KEYs. When a column is set to UNIQUE KEY, no two rows can have the same data in that column. PRIMARY KEY does not allow NULL values, but UNIQUE KEY does.

Modify the table`ALTER TABLE table_name ADD PRIMARY KEY(column_name, …)

Summary, the same points:
•PRIMARY KEY and UNIQUE KEY are both used Ensure that the data on the column is a prototype
•You can add

to one or more columns. Differences:
•There can only be one PRIMARY KEY in the same table, but there can be multiple UNIQUE KEY
•
PRIMARY KEY cannot have a null value, but UNIQUE KEY can. If 1 or more columns of PRIMARY KEY are NULL, the columns are automatically changed to NOT NULL when increasing the PRIMARY KEY. UNIQUE KEY has no requirements for columns and is implemented through a reference index. If the inserted values ​​are all NULL, then according to the principle of the index, all NULL values ​​will not be recorded in the index, so when inserting all NULL values, there can be duplicates. Others cannot insert duplicate values.

alter table t add constraint uk_t_1 UNIQUE (a,b); insert into t (a ,b ) values ​​(null,1); # Cannot repeat insert into t (a ,b ) values ​​(null,null );#Can be repeated

In MySQL, for a PRIMARY KEY column, MySQL has automatically established a UNIQUE INDEX for it, and there is no need to repeatedly create an index on it.

An explanation about PRIMARY KEY and UNIQUE INDEX on the Internet:

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).

Operation Index

Creating index files will occupy disk space.
CREATE INDEX IndexName ON mytable(username(length));

If it is CHAR, VARCHAR type, length can be less than the actual length of the field; if it is BLOB and TEXT type, length must be specified.

Create index when creating table:

CREATE TABLE mytable( 
 ID INT NOT NULL,  
 username VARCHAR(15) NOT NULL,
 INDEX [INDEXName] (username(length)) 
);

Delete index

DROP INDEX [INDEXName] ON mytable;

The above is the detailed content of Detailed explanation of the differences between KEY and PRIMARY KEY and UNIQUE KEY and INDEX in MySQL. For more information, please follow other related articles on the PHP Chinese website!