FK Constraints Database vs FK Software Constraints

Question

Although I am targeting MySQL / PHP, for the sake of my questions, I would just like to apply this as a whole to any relational database that is used in conjunction with a modern programming language. Another assumption would be that the language uses a modern structure, which at some level will handle foreign key constraints implicitly or have the means to do this explicitly.

My questions:

• What are the pros and cons of creating FK constraints in the database itself, rather than managing them at the application level?

• From a design point of view, should they ever be used together or could cause conflict?

• If they should not be used together, then what is considered “best practice” as to which approach to use?

Note: This is a design theory question. Due to the wide variety of technologies that can be used to satisfy the implementation, I am not interested in any features of the implementation.

Answer 1

What are the pros and cons of creating FK constraints in the database itself, rather than managing them at the application level?

In a parallel environment, in practice it is difficult to implement referential integrity in the application code, so that it is correct and with good performance.

If you do not use the lock very carefully, you can participate in the race, for example:

• Imagine there is one row in the parent table and there are no matching rows in the child element.
• Transaction T1 inserts a row into the child table, but does not commit yet. He can do this because there is a corresponding row in the parent table.
• Transaction T2 deletes the parent row. He can do this, since from his point of view there are no child rows (T1 has not yet been fixed).
• Transmission T1 and T2.
• At this point, you have a child string without a parent (i.e. broken referential integrity).

To fix this, you can lock the parent row from both transactions, but it is likely to be less efficient compared to the highly optimized FK implemented in the DBMS itself.

In addition, all your clients must adhere to the same "blocking protocol" (one wrong client is enough to display data). And complexity increases quickly if you have multiple levels of nested FK or diamond-shaped FK. Even if you implement referential integrity in triggers, you only solve the "one bad client" problem, but the rest remains.

Another nice thing about FK at the database level is that they usually support reference actions like ON DELETE CASCADE. And all this is simple and self-documenting, unlike referential integrity, looping inside the application code.

From a design point of view, should they ever be used together or could cause conflict?

You should always use FK at the database level. You can also use application-level “pre-checks” if it benefits your user (i.e. you don't want to wait until the actual INSERT / UPDATE / DELETE notifies the user), but you should always specify the code as if INSERT / UPDATE / DELETE may fail even if your application level check passed.

If they should not be used together, what is considered “best practice” as to which approach to use?

As I said, always use FK at the database level. If you wish, you can also use FK at the application level "from above."

Answer 2

How familiar are you with the design of the database and the concept of the foreign key as a whole? FK is a column (s) in one table that identifies a row in another table. (I'm sure you already know this.) So the FK constraint is what exists in the database, not in the application. To manage FK restrictions in the application, manual coding is required for the functionality that is already available in the database. So why do you want to do all this with manual labor? In addition, interaction with the database and applications and development is much more complicated due to all this additional manual coding.

IMHO's best practice is to use tools for what they created. DB takes care of the referential integrity of FK and the application does not need to deal with the internal functionality of the database. However, if your referential integrity is your main problem, and you, for example, use MySQL with the MyISAM engine, which does not support FK restrictions , then you need some manual checks in the application (or, possibly, with DB triggers that I am not familiar with) . Just keep in mind that when you perform all the checks in the application, you still need to access the database, and therefore you are using more resources than what is really needed if the database can handle referential integrity checks. (An easy solution, of course, will start by using the InnoDB mechanism , but I will stay here until this answer becomes too product-oriented).

So, some pros in order to allow the DB to handle the FK constraint would be as follows:

• You do not need to think about it.
• You do not need to manually specify anything additional.
• An application uses fewer resources and contains less code and therefore ...
• ... maintaining and developing both the database and the application is much simpler (for example, application developers do not need to understand the concepts and functionality focused on the database so much, let the database experts make FK thinking, etc. ...).

Answer 3

What are the pros and cons of creating FK constraints in a database as opposed to managing them at the application level?

Some of the benefits of using db-forced FKs:

• Separation of a shemei from a code.

• Decrease application code

• The programmer will not have any problems with the FK rules.

• Makes other applications that integrate with db follow fk rules.

Some of the disadvantages associated with db-forced FK.

• It’s not easy to break if you have a special case

• If the data is invalid, errors may be reset. The application must be encoded to gracefully handle such errors (especially batch errors).

• The definition of an FK with referential integrity rules must be defined and encoded carefully. You do not want to cascade delete 1,000,000 lines on the Internet.

• They cause an implicit check, even if you do not want this check to happen, because you know that the parent string must exist. This is probably a trivial performance impact. Performance is a problem when loading huge amounts of data in batch loads and in OLAP / Data Warehouse systems. Special download tools are used, and restrictions, such as forced FK databases, are usually disabled at boot time.

From a design point of view, should they ever be used together or cause a conflict?

You can use them together for some reason. As I mentioned earlier, you may have special cases in your data that you cannot define for FK. In addition, there are certain cases, such as many-to-many relationships between tables that cannot be processed by FK (at least for some db engines).