MySQL primary key design principles

To learn about MySQL databases, the primary key of MySQL cannot be left blank. The following describes the design principles of the primary key of MySQL. We hope that you can have a deeper understanding of the primary key of MySQL.

Relational databases depend on primary keys. It is the cornerstone of the physical database mode. The primary key has only two purposes on the physical layer:
1. uniquely identifies a row.
2. As an object that can be effectively referenced by a foreign key.

Based on the above two purposes, the following provides some principles that I follow when designing the primary keys of the physical layer:

1. the MySQL primary key should be meaningless to the user. If you see data in a connection table that represents many-to-many relationships and complain about its usefulness, it proves that its primary key is well designed.

2. the MySQL primary key should be a single column to improve the efficiency of connection and filtering operations.

Note: people who use composite keys usually have two reasons for self-release, which are both incorrect. The first is that the primary key should be of practical significance. However, making the primary key meaningful only makes it convenient to artificially damage the database. The second is that two external keys can be used as the primary key in the connection table describing multiple-to-multiple relationships. I also oppose this approach because: A composite primary key often leads to a bad foreign key, that is, when the connected table becomes another master table of the slave table, it becomes part of the primary key of the table according to the second method above. However, this table may become another master table of another slave table, and its primary key may be a part of another primary key of the slave table. If this is passed on, the closer it is to the back of the slave table, the primary key will contain more columns.

3. Never update the MySQL primary key. In fact, because the primary key only identifies a row and has no other purposes, there is no reason to update it. If the primary key needs to be updated, it indicates that the primary key should be meaningless to the user.

Note: This principle is not applicable to data that often needs to be sorted during data conversion or multi-database merger.

4. MySQL primary keys should not contain dynamically changed data, such as timestamps, creation time columns, and modification time columns.

5. the MySQL primary key should be automatically generated by a computer. If a person intervene in the creation of a primary key, it will have a meaning other than a unique row. Once this boundary is crossed, the motive for modifying the primary key may be generated, this system is used to link record rows and manage record rows. It will fall into the hands of people who do not know the database design.

MySQL partition functions and restrictions

Two MySQL partitions

MySQL environment variable configuration instructions

Mysql Stored Procedure FAQs

Use of mysql ifnull Functions