Differences between KEY PRIMARY KEY UNIQUE KEY and INDEX in MySQL

The problems raised in the topic can be solved step by step. KEY and INDEX are synonymous in MySQL. Then this problem can be reduced to the difference between PRIMARY KEY, UNIQUE KEY and INDEX. And these three are just the index division, primary key index, only 1 index and ordinary index (INDEX).

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

When you create an index, you need to make sure that the index is the condition that applies to the SQL query statement (1 is generally the condition of the WHERE clause). In fact, the index is also a table, which holds the primary key and index fields and points to the records of the entity table.

Indexes also have their drawbacks: While they speed up queries, they slow down table updates such as INSERT, UPDATE, and DELETE on tables. Because when updating the table, MySQL should not only save the data, but also save the index file under 1.

Difference between KEY and INDEX in MySQL

KEY is usually synonymous with INDEX. PRIMARY KEY can also be specified only as KEY if the key attribute PRIMARY KEY is given in the column definition. The purpose of this is to be compatible with other database systems. PRIMARY KEY is a only 1 KEY, in which 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.

KEY is a key value, which is a part of relational model theory. For example, there are primary keys (PRIMARY KEY) and foreign keys (Foreign KEY), which are used for data integrity check and uniqueness constraint. While INDEX is at the implementation level, for example, any column of a table can be indexed, so when the indexed column is in Where condition in SQL statement, fast data positioning can be obtained, thus fast retrieval. As for UNIQUE INDEX, it only belongs to one of INDEX. The establishment of UNIQUE INDEX means that this column of data cannot be repeated. Guess MySQL can make a special optimization for UNIQUE INDEX index.

Therefore, when designing the table, KEY is only at the model level, and when query optimization is needed, it is enough to index the related columns.

KEY

KEY is the physical structure of database, which contains two meanings: 1 is constraint, which focuses on constraining and standardizing the structural integrity of database; 2 is index and auxiliary query.
• primary key has two functions, 1 is the constraint function (constraint), which is used to standardize a storage primary key and uniqueness, but at the same time, an index is established on this key;
• unique key also has two functions, one is the constraint function (constraint), which regulates the uniqueness of data, but at the same time, an index is established on this key;
• foreign key also has two functions, one is the constraint function (constraint), which regulates the referential integrity of data, but at the same time, an index is established on this key;

It can be seen that key has the meanings of constraint and index at the same time.

INDEX

INDEX is also the physical structure of the database, but it only serves as an auxiliary query, which takes up additional space when it is created. Indexes are divided into prefix indexes, full-text indexes and so on. An index is only an index and does not constrain the behavior of an index field.

Differences between PRIMARY KEY and UNIQUE KEY

PRIMARY KEYs (primary key) and UNIQUE KEYs (only one key constraint) are similar, PRIMARY KEY is usually one column, and possibly multiple columns, and it is usually up to him to decide one row of data (row). You can only have one PRIMARY KEY per table, but you can have many UNIQUE KEY. When column 1 is set to UNIQUE KEY, no two rows can have the same data on that column. PRIMARY KEY does not allow NULL values, but UNIQUE KEY does.

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

To sum up, similarities:
• ES 138EN ES 139EN and ES 140EN ES 141EN are used to ensure that the data on the column is a prototype
Can be added to 1 column or more columns

Difference points:
There can only be one PRIMARY KEY and multiple UNIQUE KEY in the same table
•
PRIMARY KEY cannot have null values, UNIQUE KEY can have null values. If one or more columns of PRIMARY KEY are NULL, the columns automatically change to NOT NULL when PRIMARY KEY is added. However, UNIQUE KEY does not require columns to be implemented by referring to the index. If all the inserted values are NULL, according to the principle of index, all NULL values are not recorded on the index, so when inserting all NULL values, there can be duplicates, 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); # Do not repeat insert into t (a, b) values (null, null); # Can be repeated

In MySQL, for one column of PRIMARY KEY, MySQL has automatically established UNIQUE INDEX, so there is no need to repeatedly establish indexes on it.

One paragraph of online explanation 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 (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).

Operational index

Index files that take up disk space when indexing.
CREATE INDEX IndexName ON mytable(username(length));

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

Create an index when creating a table:

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

Drop index

DROP INDEX [INDEXName] ON mytable;