The difference between KEY, PRIMARY KEY, UNIQUE KEY, and INDEX in MySQL

The problem raised in the title can be broken down and solved step by step. In MySQL KEY and INDEX are synonymous. Then this question can be simplified to the difference between PRIMARY KEY, UNIQUE KEY and INDEX. These three are exactly 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 the columns of JOIN, WHERE, and ORDER BY clauses.

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

Indexes also have their disadvantages: although indexes increase query speed, they slow down table updates, such as INSERT, UPDATE, and DELETE. Because when updating a table, MySQL not only needs to save the data, but also 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 just KEY 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, in which case all keyword columns must be defined as NOT NULL. If the columns are not explicitly defined as NOT NULL , MySQL should define them implicitly.

KEY, or key value, is part of the relational model theory, such as primary key (PRIMARY KEY), foreign key (Foreign KEY), etc., which are used for data integrity check and uniqueness constraint, etc. INDEX is at the implementation level. For example, you can create an index for any column in a table. Then, when the indexed column is in the Where condition in an SQL statement, you can quickly locate data and retrieve it. As for UNIQUE INDEX, it is just a type of INDEX. Creating a UNIQUE INDEX indicates that the data in this column cannot be repeated. I guess MySQL can make further special optimizations for UNIQUE INDEX type indexes.

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

KEY

KEY is the physical structure of the database and has two meanings. One is constraint, which focuses on constraining and regulating the structural integrity of the database; the other is index, which assists in query.
•The primary key has two functions. One is the constraint, which is used to regulate a storage primary key and uniqueness, but it also creates an index on this key.
•The unique key also has two functions. One is the constraint, which regulates the uniqueness of the data, but it also creates an index on this key.
•Foreign keys also have two functions. One is the constraint function, which regulates the referential integrity of the data, but at the same time, an index is also established on this key;

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

INDEX

INDEX is also a physical structure of the database, but it only serves as an auxiliary query and it takes up 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 the indexed field.

The difference between PRIMARY KEY and UNIQUE KEY

PRIMARY KEYs and UNIQUE KEYs are similar. A PRIMARY KEY is usually a single column, but it can also be multiple columns, and it usually determines a row of data. A table can have only one PRIMARY KEY, but can have many UNIQUE KEYs. When a column is set as UNIQUE KEY, no two rows can have the same data in the column. PRIMARY KEY does not allow NULL values, but UNIQUE KEY does.

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

In summary, the similarities:
• PRIMARY KEY and UNIQUE KEY are used to ensure that the data in the column is original
• You can add one or more columns

Differences:
• A table can have only one PRIMARY KEY, but can have multiple UNIQUE KEYs
•
PRIMARY KEY cannot have null values, but UNIQUE KEY can. If one or more columns of the PRIMARY KEY are NULL, the columns are automatically changed to NOT NULL when the PRIMARY KEY is added. UNIQUE KEY has no requirements on columns and is implemented by referring to the index. If the inserted values ​​are all NULL, then according to the principle of the index, all NULL values ​​are not recorded in the index. Therefore, when inserting all NULL values, there can be duplicates, but other duplicate values ​​cannot be inserted.

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

In MySQL, for a PRIMARY KEY column, MySQL has automatically created a UNIQUE INDEX for it, so there is no need to create an index on it again.

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

Note that "PRIMARY" is called PRIMARY KEY not INDEX.
KEY is something on the logical level, describes your table and database design (ie enforces referential integrity …)
INDEX is something on the physical level, which helps improve access time for table operations.
Behind every PK there is (usually) UNIQUE INDEX created (automatically).

Operation Index

Building an index takes up disk space for index files.
CREATE INDEX IndexName ON mytable(username(length));

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

Create an index when you create a table:

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

Deleting an Index

DROP INDEX [INDEXName] ON mytable;