What is mysql logical primary key

mysql logical primary key refers to the field or field combination used to identify a record in the database, but it is not unique. Logical primary keys are usually used for data queries and data operations. The logical primary key can be any field with identifying properties. For example, in the user table, the user name can be used as the logical primary key because it can be used to identify a record, but it is not unique.

The operating system of this tutorial: Windows 10 system, mysql version 8.0, Dell G3 computer.

1. Business primary key

The business primary key refers to the field or field combination used to uniquely identify a record in the database. Business primary keys are often used as the primary keys of indexes to speed up database queries and data operations. The selection of business primary keys needs to be based on actual business needs and data characteristics. Fields with uniqueness, stability and simplicity are usually selected as business primary keys. In the user table, the user ID can be used as the business primary key because of its uniqueness, stability and simplicity.

2. Logical primary key

The logical primary key refers to the field or field combination used to identify a record in the database, but it is not unique. Logical primary keys are usually used for data queries and data operations. The logical primary key can be any field with identifying properties. For example, in the user table, the user name can be used as the logical primary key because it can be used to identify a record, but it is not unique.

3. The difference between business primary key and logical primary key

The biggest difference between business primary key and logical primary key is uniqueness. The business primary key must be unique and used to identify the uniqueness of a record; the logical primary key does not require uniqueness, as long as it can identify a record. In addition, business primary keys are usually used as the primary keys of indexes to speed up database queries and data operations; logical primary keys are usually used for data queries and data operations.

4. How to use business primary keys and logical primary keys

The methods of using business primary keys and logical primary keys are as follows:

1. Create business primary keys and Logical primary key

When creating a table, you need to define the business primary key and logical primary key in the table structure. When creating a user table, you can define the user ID as the business primary key and the user name as the logical primary key.

2. Set the attributes of the business primary key and logical primary key

After defining the business primary key and logical primary key in the table structure, you need to set their attributes. You can use the AUTO_INCREMENT attribute to set the auto-increment of the business primary key to ensure uniqueness; and in the logical primary key, you can use the UNIQUE attribute to set the uniqueness of the field.

3. Use business primary keys and logical primary keys for data operations

When performing data operations, you need to use business primary keys and logical primary keys to identify records. When querying user information, you can use the user ID as the business primary key to query; and when updating user information, you can use the user name as the logical primary key to update.

5. Application of business primary keys and logical primary keys

In actual development, business primary keys and logical primary keys are widely used. In an e-commerce website, the product ID can be used as a business primary key to uniquely identify a product; while the product name can be used as a logical primary key to identify a product, but it is not unique. In social networking sites, user ID can be used as a business primary key to uniquely identify a user; and user nickname can be used as a logical primary key to identify a user, but it is not unique.

In short, business primary keys and logical primary keys are widely used in MySQL databases and play an important role in improving the performance and reliability of the database. In actual development, developers need to reasonably select business primary keys and logical primary keys based on actual business needs and data characteristics to achieve the best results.

Extended information:

What is the primary key?

MySQL A primary key is a single field or combination of fields that uniquely identifies each record in a table. If a column contains a primary key constraint, it cannot be null or empty. A table may have duplicate columns, but it can only contain one primary key. It always contains unique values ​​in the column.

When you insert a new row into the table, the primary key column can also use the AUTO_INCREMENT attribute to automatically generate a sequence number for the row. After MySQL defines a primary key in the table, it will automatically create an index named "Primary". Since it has associated indexes, we can say that primary key makes query performance fast.

MySQL Primary Key (primary key) rules

The primary key column value must be unique. Each table can contain only one primary key. Primary key columns cannot be null or empty. MySQL does not allow us to insert new rows using existing primary keys. It is recommended that primary key columns use the INT or BIGINT data type.

We can create a primary key in two ways:

CREATE TABLE statement ALTER TABLE statement

The above is the detailed content of What is mysql logical primary key. For more information, please follow other related articles on the PHP Chinese website!