The choice of the database facing the different business logic constraint conditions

Data table Constraints I think it is very useful, at least in the database optimization is still used more, can greatly improve the efficiency of retrieval, the role is more obvious, the other point, the table of constraints can be to some extent simplifying the code side of the business logic, which is stored on the DBMS, Its maintainability I must have the Korean high, this general type of database inside, our common constraints are: primary key, foreign key, is empty, unique, and so on, these four classes are more common constraints, the essence of my absolute constraint should be for the real business logic and service, otherwise there is no meaning, so, In the face of different business logic to analyze each:
1: Where to use the primary key:
The meaning of the primary key is unique and not empty, so according to this specification, can satisfy the business logic has a lot of, columns such as our common ID value, must exist and does not exist in the case of empty, in general, a table will have a primary key, at least after the operation of the future, Because both the DQL statement and the DML statement require a unique, non-nullable identifier as the index value, and without special logic requirements, the primary key is automatically added: auto_increment; this also conforms to the general logic requirements, but also can improve the retrieval speed.
2: When to use foreign keys:
The foreign key of the table is actually a constraint on the table for the main table, which is in the range required by the external key field, and represents a master-slave relationship, such as the relationship between the department table and the employee table. The Department table is a primary table, there is a primary key value, then if the employee table wants to establish a master-slave relationship with the main table to establish a foreign key, here must understand a place, what so-called establishment of foreign keys are built in from the table, the main table and normal operation no difference, the foreign key from the table is a specified primary table , so that constitutes a binding relationship, such as the relationship between an employee belongs to which department of the distance can be established, of course, it is also important to note that the foreign key is not necessarily a primary key to the primary table, or a unique value, is the foreign key is unique, of course, the business logic, as well as the foreign key value can be empty As for this to start I also do not understand, since the establishment of foreign keys to establish the constraints that why can also be empty, but I have a practical business logic to think about, if a new employee does not determine the department but need to join the database that, so the foreign key is null value in line with the general business logic.
3: Under what circumstances not NULL is used
This meaning is very good understanding, do not introduce, but business logic is necessary to say, before this constraint I use is not very reasonable, no matter what I am not empty, although the efficiency is high, but this is very unscientific, such as a registry will have a lot of information, What is empty what does not have to be empty according to the actual business logic to think about, need to think about it, the first should be based on business needs to choose, such as user name, password, mailbox, age, birthday, ID number of these several registration table field information, first user name, password, e-mail (usually login or retrieve password to use) is essential, so it must not be empty, and the age and birthday in general without special needs can not be filled, if set is not empty, then have to force users to fill it, there is the ID card number, this is actually not quite appropriate, ID number as a user more privacy things, if the site needs to have to be the identity number such information can not be empty, the other can be empty, the default.
4: What is the use of unique:
The meaning of this constraint is unique, but can be empty, is null and the primary key is the only difference, this constraint is relatively useful, such as the department's name, of course, is unique, but some new departments do not have a name can be empty, but there will certainly be a need for a table to multiple primary keys, This can be replaced by a unique combination of NOT NULL, after all, unique can be used more than one, but if you want to specify the conditions for uniqueness is also possible, which is also allowed.

Summing up: all constraints should be established according to the actual needs of the business

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

The choice of the database facing the different business logic constraint conditions