Does performance decrease integrity?

Question

In a conversation with a friend, I had to hear two things -

• Using restrictions results in a slight decrease in performance. eg. Consider the uniqueness constraint. Before inserting the DBMS, it would be necessary to check the uniqueness in all existing data, thereby causing additional calculations.

• He suggested making sure that these restrictions are handled by the application-level logic itself. eg. Delete rows from both tables yourself, instead of setting external integrity constraints, etc.

At first I am a little logical for me, but the second seems pretty wrong intuitively. I do not have enough experience working in a DBMS to really judge these claims.

Q. Is claim 1 correct? If so, then, it is claimed, 2 is the right way to handle such scenarios?

Answer 1

TL DR

If your data needs to be correct, you need to enforce restrictions, and if you need to enforce restrictions, allowing the database to do this for you, you will be faster than anything else (and probably more correctly).

Example

An attempt to implement something like key uniqueness at the application level can be completed correctly or quickly, but not for both. For example, let's say you want to insert a new line. A naive application-level algorithm might look something like this:

• ( ) .
• , .

/ . , - 1 2 presto: , !

, , , - , , , .

OTOH, , , , , .

.

---

, , (, ), , , ?

- , "-" ( "" ) , ...

- , . , .

Answer 2

1 , 2 , , .

- . - , .

# 2 :

• concurrency - , db 1 ? , . , . , , , ?

• , . - , , , . , - , , db .

• concurrency. - , , , .. . db , .

MySQL , , . , MySQL , , - . , , - , , , , , . .

- , MySQL ( ) . , , FK . , , .

ACID . , , , MySQL InnoDB , , - . , , MySQL.

TL; DR: .

Answer 3

, , .

, . : . : , , , . , , . " " , " ", .

. , , , . , , .

Answer 4

. . :

• , "" , .
• : " ?" DML. , . , , .

, , , . , DML, .

. ( " !" ), , .

. . , , ? . , . , , . .

When designing a database, data integrity is the number one priority. Never sacrifice this for performance, especially since the performance of a well-designed database is not often a problem, and even when this is the case, there are too many ways to improve performance that is not related to removing restrictions (or denormalizing, another mistake many still make for improve OLTP system performance).