Database selection of different business logic constraints and database business logic Constraints

Database selection of different business logic constraints and database business logic Constraints

I think the constraints on data tables are still very useful. At least there are many database optimizations, which can greatly improve the retrieval efficiency and play a significant role, table constraints can simplify the business logic of the program code to some extent, which is stored on the DBMS, and its maintainability is definitely Korean-high. In this general type of database, common constraints include: primary key, foreign key, null, and unique. These four are common constraints, the essence of my absolute constraint should be to serve the real business logic, otherwise it will be meaningless. Therefore, we will analyze different business logic one by one:
1: When to use the primary key:
The meaning of a primary key is unique and not empty. Therefore, according to this specification, there are many business logic that can be satisfied. Columns such as common ID values must exist and do not exist as null, generally, a table has a primary key, at least for subsequent operations, because no matter whether it is a dql statement or a dml statement, a unique non-null identifier is required as the index value, and, without special logic requirements, the auto-increment sheet of the primary key is required: auto_increment; this meets the general logic requirements and improves the retrieval speed.
2: When to use the foreign key:
The foreign key of the table is actually a constraint on the table of the master table. To put it bluntly, it is within the range required by the foreign key field, indicating a master-slave relationship, for example, the relationship between a department table and an employee table. A department table is a master table with a primary key value. If the employee table wants to establish a master-slave relationship with the master table, a foreign key must be created, what are the so-called foreign keys that are created in the slave table? There is no difference between the master table and normal operations. The Foreign keys created in the slave table are a relationship that specifies the primary keys of the master table, in this way, a constraint relationship is formed. For example, the relationship between an employee and the department can be established. Of course, you must note that, the foreign key is not necessarily the primary key pointing to the primary table, it may also be the unique value, it is the unique foreign key, of course, in line with the business logic, and the foreign key value can also be blank, as for this, I didn't understand it at the beginning. Since the foreign key has been set up to establish constraints, why can it be empty? But I thought about it based on the actual business logic, what should I do if a new employee does not determine the Department but needs to join the database? Therefore, a null foreign key is in line with the general business logic.
3: When to use not null
The meaning of this is very understandable and I will not introduce it more. However, it is still necessary to explain the business logic. In the past, this constraint was not very reasonable, no matter what it is, I am not empty. Although the efficiency is high, it is very unscientific. For example, a registry contains a lot of information, you have to think about what is blank and what is not empty according to the actual business logic. To think about this, you should first choose based on business needs, such as user name, password, email address, age, birthday, ID card number of the Registry field information, first user name, password, mailbox (generally used to log on or retrieve the password) is essential, so it must not be blank, however, age and birthday can be left blank without special requirements. Do you have to force the user to enter the ID card number if it is not set to be empty, in fact, this is not appropriate. The ID card number is a private thing for users. If the website requires an ID number, it can be left blank. Otherwise, it can be left blank by default.
4: when to use unique:
This constraint is unique but can be empty. It is the only difference from the primary key. This constraint is relatively useful, such as the Department name, which is of course unique, however, some new departments can be empty without names, but sometimes this is the case. A table wants multiple primary keys, which can be replaced by unique and not null, after all, unique can use multiple. However, if you want to specify conditions for uniqueness, it is acceptable.

Summary:All constraints should be established based on actual business needs.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.