Analysis of the relationship and difference between MySQL primary key and index
- 2020-05-15 02:25:23
- OfStack
A relational database is dependent on the primary key, which is the cornerstone of the database physical schema. Primary keys have only two USES on the physical plane:
Only 1 marks 1 line.
As an object that can be effectively referenced by a foreign key.
An index is a special file (the index on the InnoDB data table is a component of the table space) that contains a reference pointer to all the records in the table. Here are some differences and associations between primary keys and indexes.
1. Primary key 1 must be a unique index, and a unique index is not a primary key.
A primary key is a property or group of properties that can identify a row in a table by only 1. A table can only have one primary key, but can have multiple candidate indexes. Since the primary key can identify a row of records by only 1, you can ensure that there are no errors when data is updated or deleted. In addition to the above functions, primary keys often form referential integrity constraints with foreign keys to prevent the occurrence of non-1 data. Primary keys play an important role in database design.
The primary key can ensure that the record is only 1 and the primary key field is not empty. The database management system automatically generates only 1 index for the primary key, so the primary key is also a special index.
2. A table can have more than one unique index, but only one primary key.
3. Null values are not allowed for primary key columns, while null values are allowed for exclusive index columns.
4. Indexes can speed up queries.
Primary key and index are key, actually, but a primary key is the logical key, the index is a physical key, which means that the primary key does not exist, the existence and index in the database, the primary key is to build 1, mainly used to avoid 1 table have the same record, index can not build 1, but if you need to query the table operation, is the best building, so that we can accelerate the speed of retrieval.
FAQ
1. What is the primary key?
Let me give you a little bit of detail. You read the book, if you haven't read the book, turn it over once, see if it has a page number per page, and our data table's primary key is the same thing as the page number, you get the idea.
2. What is an index?
We also take the book for example, the index is equivalent to the directory of the book, with the directory we can quickly know the basic content and structure of the book, the data index is also 1, it can speed up the query of the data table.
3. Primary key primary index analogies, and what do they do?
The primary key is used to identify database record singleness, does not allow duplicate records, and the key value cannot be empty. The primary key is also a special index.
Only one primary key is allowed in a table, but multiple indexes are allowed.
Using the primary key database will automatically create the primary index, you can also create the index on the non-primary key, convenient query efficiency.
An index can speed up a query by acting as a directory of dictionaries that can be quickly queried to get the desired results without the need for a full table scan.
The value of the outer index of the primary key index can be empty.
A primary key can also be composed of multiple fields, forming a composite primary key, which must also be a 1-only index.
A one-only index means that the index value is one-only and can be composed of one or more fields. A table can have multiple one-only indexes.
Only 1 marks 1 line.
As an object that can be effectively referenced by a foreign key.
An index is a special file (the index on the InnoDB data table is a component of the table space) that contains a reference pointer to all the records in the table. Here are some differences and associations between primary keys and indexes.
1. Primary key 1 must be a unique index, and a unique index is not a primary key.
A primary key is a property or group of properties that can identify a row in a table by only 1. A table can only have one primary key, but can have multiple candidate indexes. Since the primary key can identify a row of records by only 1, you can ensure that there are no errors when data is updated or deleted. In addition to the above functions, primary keys often form referential integrity constraints with foreign keys to prevent the occurrence of non-1 data. Primary keys play an important role in database design.
The primary key can ensure that the record is only 1 and the primary key field is not empty. The database management system automatically generates only 1 index for the primary key, so the primary key is also a special index.
2. A table can have more than one unique index, but only one primary key.
3. Null values are not allowed for primary key columns, while null values are allowed for exclusive index columns.
4. Indexes can speed up queries.
Primary key and index are key, actually, but a primary key is the logical key, the index is a physical key, which means that the primary key does not exist, the existence and index in the database, the primary key is to build 1, mainly used to avoid 1 table have the same record, index can not build 1, but if you need to query the table operation, is the best building, so that we can accelerate the speed of retrieval.
FAQ
1. What is the primary key?
Let me give you a little bit of detail. You read the book, if you haven't read the book, turn it over once, see if it has a page number per page, and our data table's primary key is the same thing as the page number, you get the idea.
2. What is an index?
We also take the book for example, the index is equivalent to the directory of the book, with the directory we can quickly know the basic content and structure of the book, the data index is also 1, it can speed up the query of the data table.
3. Primary key primary index analogies, and what do they do?
The primary key is used to identify database record singleness, does not allow duplicate records, and the key value cannot be empty. The primary key is also a special index.
Only one primary key is allowed in a table, but multiple indexes are allowed.
Using the primary key database will automatically create the primary index, you can also create the index on the non-primary key, convenient query efficiency.
An index can speed up a query by acting as a directory of dictionaries that can be quickly queried to get the desired results without the need for a full table scan.
The value of the outer index of the primary key index can be empty.
A primary key can also be composed of multiple fields, forming a composite primary key, which must also be a 1-only index.
A one-only index means that the index value is one-only and can be composed of one or more fields. A table can have multiple one-only indexes.