What are the differences among KEY, primary key, unique key, and INDEX in MySQL? mysqlunique

What are the differences among KEY, primary key, unique key, and INDEX in MySQL? mysqlunique

You can split the questions raised in the questions to solve them step by step. In MySQL, KEY and INDEX are synonymous. This problem can be simplified to the difference between primary key, unique key and INDEX. And the three are exactly the INDEX Division, primary key INDEX, unique INDEX and common INDEX ).

Use INDEX to accelerate data reading from the database. The INDEX is usually added to the columns of the JOIN, WHERE, and order by clauses.

When creating an index, make sure that the index is a condition applied to the SQL query statement (generally used as a condition for 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 object table.

The index also has its disadvantages: although the index improves the query speed, it will reduce the speed of updating the table, such as performing INSERT, UPDATE, and DELETE operations on the table. When updating a table, MySQL not only needs to save data, but also stores the index file.

Differences between keys and indexes in MySQL

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

KEY

The KEY is the physical structure of the database. It has two meanings: one is constraints, focusing on constraints and standardizing the structural integrity of the database, and the other is indexes to assist in queries.
• The primary key has two functions: one is the constraint function (constraint), which is used to standardize a primary key and the uniqueness, but an index is also created on this key;
• Unique key also has two functions: one is constraint, which standardizes the uniqueness of data, but also creates an index on this key;
• Foreign key also has two functions: one is constraint, which standardizes the integrity of data references, but also creates an index on this key;

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

INDEX

INDEX is also the physical structure of the database, but it only serves as a secondary query. It occupies additional space during creation. Indexes include prefix indexes and full-text indexes. An index is an index and does not constrain the behavior of an index field.

Difference between primary key and UNIQUE KEY

PRIMARY KEYs (primary key) and UNIQUE KEYs (unique key constraint) are similar. primary key is usually a column and may have multiple columns. It is usually up to him to determine a row of data (row ). A table can have only one primary key, but many UNIQUE keys. When a column is set as the unique key, there cannot be two rows with the same data in the column. Primary key cannot have NULL values, but unique key can.

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

Summary, similarities:
• Both primary key and unique key are used to ensure that the data on the column is prototype.
• You can add one or more columns

Differences:
• Only one primary key can exist in the same table, and multiple UNIQUE keys can exist.
•
Primary key cannot have null values, and unique key can have values. If one or more columns of the primary key are NULL, the column is automatically changed to not null when the primary key is added. The unique key does not require a column to be 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

In MySQL, for a primary key column, MySQL has automatically created a unique index for it without having to repeat it.

An online explanation of 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 ).

Operation Index

Index files that occupy disk space.
Create index IndexName ON mytable (username (length ));

For CHAR and VARCHAR types, the length can be smaller than the actual length of the field. For BLOB and TEXT types, the length must be specified.

Create an index when creating a table:

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

Delete Index

DROP INDEX [INDEXName] ON mytable;