Shulou(Shulou.com)06/01 Report--

Let's learn about the key and index relationship in mysql. I believe you will benefit a lot after reading it. The text is not much in essence. I hope the key and index relationship in mysql is what you want.

I. Primary key index relationship

1. Primary key: the only function of a primary key is to uniquely identify a row of data in a table. Divided into a single primary key and a federated primary key:

Single primary key: a row can be uniquely identified with only one column.

Union primary key: when using a column that can no longer uniquely identify a row, it is necessary to use multiple columns to uniquely identify a row, that is, a federated primary key. Multiple fields of the joint master key cannot be the same at the same time

two。 Index: the function of index is to improve the retrieval speed of data, which is divided into single index and joint index:

Single index: only use a column of data as the index, the default is index index, this column can contain duplicate data; if there is no duplicate data in a column, it is better to set an index in the form of unique, which is faster than index index, and use fulltext index on text data.

Joint index: in order to further improve the retrieval speed, when each retrieval needs to use multiple columns at the same time, the multi-column can be set as a joint index to improve the indexing speed. According to whether the multiple columns are unique or not, they can also be divided into index index and unique index.

It must be noted that the use of federated indexes should not break the use of fields. For example, only a | ab | abc can use federated index fields in abc federated indexes.

3. The primary key must be the index, but the index is not necessarily the primary key. A table can have only one primary key or federated primary key, but can have multiple indexes.

The primary key field must not be empty, but the index field can be empty.

Second, the relationship between foreign keys and main keys

1. A foreign key is a constraint marking that allows the values of fields in the table to be selected in the fields in the reference table.

two。 Conditions for creating foreign keys

The referenced field must be a primary key

The reference field and the referenced field must be the same data type

The storage engine of the table must be innodb

III. Grammatical format

1. Create a command format for the primary key

1.1. Create the key create table table name (field list, primary key (field name that needs to be set to the primary key) auto_increment when creating the table; # auto_increment is a keyword that needs to be set to auto-grow. This keyword is not needed if the key is not an integer type or does not need to grow automatically. When you need to set a federated primary key, multiple field names that need to be set as primary keys are separated by commas, and auto-increment `or create table table names (key field type primary key auto_increment, field list) cannot be used. Create a master key on a table that has already been created (if the data already exists in the table and the data violates the primary key constraint, you cannot create the master key, you need to resolve the data conflict before creating it) alter table table name add primary key (field name that needs to be set to the primary key) auto_increment

two。 Create a command format for foreign keys

2.1. Create a foreign key when creating a table (meet the conditions for creating a foreign key) create table table name (field list, foreign key (fields that need to create foreign key constraints) references the table name of the referenced table (referenced field of the referenced table) on update cascade on delete cascade) engine=innodb; if the foreign key needs to have a unique label, you can also add the main key constraint 2.2. Create a foreign key alter table table name add foreign key (field name) references reference table name (reference field name) on update casecade on delete cascade) engine=innodb in an existing table

3. Command format for creating an index

3.1. Create an index when you create a table (there can be multiple indexes, two are added in the following example) create table table names (field list, index (already existing field names), index (existing field names); # if it is a federated index with multiple existing field names, separate them, remember that you cannot set a federated index across fields the default index name is the same as the field name. Set the field in the existing table to the index field create index index name on table name (field name) index name you can name yourself generally use the field name as the index name

4. The format of the command to create a unique index is similar to that of a normal index, as long as you ensure the uniqueness of the field record, you can use the unique keyword.

After reading this article on key and index relationships in mysql, many readers will want to know more about it. For more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.