Logical database Design-ignoring constraints (talking about foreign keys)

Some developers do not recommend using integrity constraints, and you may have heard the reasons for not using foreign keys in the following points.

1, the data update has the possibility and the constraint conflict.

2. The current database design is so flexible that referential integrity constraints are not supported.

3. Indexes created by the database for foreign keys can affect performance.

4. The database currently in use does not support foreign keys.

5, the definition of foreign key syntax is not simple, but also need to consult.

first, anti-pattern: ignoring constraints

Even if the first feeling tells you that omitting foreign key constraints can make database design simpler, more flexible, or more efficient to perform, you still have to pay the price in other ways-you have to add additional code to manually maintain referential integrity.

　　1, Integrity issues

Many people's solution to referential integrity is to ensure the relationship between data by writing specific program code. Each time you insert a new record, you need to make sure that the value referenced by the foreign key column exists in its corresponding table, and each time you delete the record, you need to ensure that all related tables are updated reasonably at the same time.

To avoid the incomplete state of a reference without a foreign key constraint, an additional select query needs to be executed before any changes take effect to ensure that these changes do not result in a reference error.

For example, before inserting a record, you need to check that the corresponding referenced record exists:

Select  from where 1

Before you can perform an insert operation.

To delete a record, you need to first confirm that no other record references the record:

Select  from where 1

Before you can perform a delete operation.

What if, in the event of a situation, this account_id happens to insert a new defect record in the execution gap of the delete operation's query and DELETE statement?

　　2. Cascading issues

Another reason many developers avoid using foreign key constraints is because they make it cumbersome to update the associated columns in multiple tables. For example, when you delete a data that is dependent on another record, you have to delete all the child records to avoid violating foreign KEY constraints:

 from where 1
 from 1

There is also an update

Update　　 Set Roleid = 2
　　Update Set where 1

To be blunt, cascading updates have trouble with cascading deletions, so simply don't use foreign keys.

If someone says it sounds like the following, then they may have used the anti-pattern described in this article.

1. How do I write this query statement to check if a value does not exist in both tables at the same time.

The usual need is to look for isolated rows, those that have been forgotten, and the rows of data records.

2. Is there a simple way to determine if the data that exists in a table is also present in the second table?

This is done to make sure that the parent record does exist, that the foreign key is done automatically, and that the foreign key is done as funny as possible using the index of the schedule.

3, foreign key? I was told not to use it because it would affect the efficiency of the database.

Performance is always a good reason to cut the design, but it always introduces more problems, even the performance problem itself. I have seen before, said to increase the foreign key additions and deletions change the speed will become slow. Keep in mind, however, that this statement only applies when the table is used for large-scale additions and deletions. For a large range of main is used to delete and change the table, no foreign key seems to be understandable. But if a table, additions and deletions are not many, this argument is not established. Instead, you avoid foreign keys, but you have to use more means to maintain data integrity.

　　In addition, most databases provide cascading updates and cascading deletions . This feature is believed to have been heard by beginners. In fact, the previous article when writing code, even if the use of cascading updates and deletions, but are not at ease, still in the program, in the absence of cascading, the first time to execute the case. For this, I just want to say to myself, since the use of cascading features, I believe it. If you don't believe it, write your own code-cascade. Then don't use it. In fact, I found that the cascading functionality of the database was much more reliable than the maintenance of the code itself.

In terms of overhead, it is true that there is a little extra overhead to add and subtract, but at the same time earning will come:

1. You do not need to perform a select check before updating or deleting records.

2, in the synchronization of changes do not need to lock the entire table.

3, no longer need to perform regular monitoring scripts to correct the inevitable garbage data, orphaned data.

Foreign keys are a handy thing to improve performance and help you maintain referential integrity in any simple or complex form of data change.

Summary: Objective analysis, I still recommend the use of foreign keys.

Logical database Design-ignoring constraints (talking about foreign keys)