Is it really necessary to define constraints in the database?

Asked

Viewed 1,643 times

14

Ruby on Rails seems to have been thought of considering that it is not necessary to define constraints (as Foreign Keys) in the database, just define them via application.

Usually the only Constraint which is created in this case is the primary key of the column id, otherwise only indexes (not necessarily unique) for performance reasons. I also don’t usually use NOT NULL, for example.

In general this facilitates development, especially in the versioning of the database.

I have to worry about integrity risks and the like when working in this way (leaving everything to the application)?

sql database relational-model
  • 3

    This makes it easier for those who develop in Ruby, and when you develop a single application. It may seem like the last Coca-Cola in the desert right now, but if ever another application built on another platform (or even Ruby, without repurposing current logic) has to perform business operations on the same database, you just won’t call Mr. David Heinemeier Hansson of Saint.

4 answers

14


On a production server, I wouldn’t rely solely on the application to ensure data integrity. The function of foreign keys is exactly to ensure the relationship between the tables of the Bank.

Assuming a bug in the relationship of two models of the application is noticed only after some time on the production server, the time for the correction at the database level will be much longer than the time to correctly implement the key relations.

Currently I develop in a small team, where the reliability of information is more important than the application itself. Soon the creation of the foreign key, define the fields as NOT NULL and a good normalization in the bank is for us the first step.

We used the Laravel here, a PHP framework that uses a ORM much like the Active Record rails. The definition of a well-structured database helps us to notice errors in the application early on, preventing several bugs from entering production.

9

It is not necessary to use them. But if you should, it is a question that depends on each scenario.

The role of constraints is to ensure the integrity of the data in the database. The decision whether or not to use it depends on how the data is generated. If you can ensure that only your properly homologated application creates, alters, updates and deletes records, then you can dispense with the constraints. But if this is not the case, it is advisable not to.

I have worked on projects where constraints were not created in the production banks, but only in the development ones, to point out the programmer’s error and assist in the homologation.

Anyway, even if you choose not to use them, remember that in many DBMS foreign keys generate or represent indexes, and in this case, these must be explicitly generated to avoid degradation in SQL operations.

  • 2

    Enforcement rules don’t guarantee data integrity. Rails doesn’t guarantee it (see my answer). To achieve this without the use of constraints would require heavy use of Locks in the application, which is complex to do and degrades performance. The simplest and most guaranteed way to maintain integrity is by using constraints in the bank.

  • I don’t know what you called "Rules in the App". I assume that it refers to the use of a pre-existing resource at a higher level of abstraction that does not allow it to guarantee integrity. But regardless of the platform, language or framework, the programmer will always have the power to dispense with abstraction and take control over the algorithms of CRUD operations and therefore can implement data integrity at the application level. About being the simplest form; I emphasize what I said: "if you should (use), it is a question that depends on each scenario".

8

As the answers have already said, it depends on the scenario you are in and the requirements of the application.

But one thing I would like to add is that in cases where it is not necessary to have well-structured and well-defined data in the database, perhaps a relational DBMS is not the best answer.

Specific use applications (for example, processing unstructured data of the type used in Big Data), or with a short lifespan (no extended use of the system is foreseen by users) and some other cases fall well with Nosql.

Anyway, by using a relational BD without taking advantage of what it has to offer you end up sub-utilizing its functionalities and running out of benefits that could get.

  • I agree with everything I said about the Nosql alternative; but I would like to emphasize that choosing not to use constraints does not mean that there is no relational structuring. Relational structure may be indispensable even if constraints are not required.

  • @Geziel Yes, I agree. The point is that if you do all the "manually" relationship control in the application, you no longer need a relational database.

  • I disagree. Data relationship is one thing and Constraint is another. Constraint is just a feature of Dbms that requires the integrity of these relationships, but even if you don’t use this feature, you can still make use of the relational structure. You can still make use of all the power of SQL, which in Nosql is non-existent or restricted. You can do joins between tables, sub-consultations, Where restrictions, etc. In a high-performance scenario, it makes enough sense to disable constraints for optimization of results, because SGDB does not need to perform checks on SQL operations.

  • 1

    @Geziel In a high-performance setting it’s just when Nosql shines. As for the joins all right, but JOIN does no constraints checking, they are used when there is data manipulation. I didn’t understand your arguments, not because of the ease of using Join.

  • 2

    I didn’t argue that. What I argued was that even using no constraints, you can use features like Join for example, and therefore there is a difference between Constraint and data linkage.

  • @Geziel All right, got it.

  • I think I understood better what you questioned. The checks I referred to are those performed by Sgdbs in UPDATE, INSERT and DELETE operations.

Show 2 more comments

4

Rails only does not guarantee data integrity. In particular, the validation of uniqueness does not guarantee that duplicate records will not be created in case of competing accesses. Rails Guides itself mentions this in http://guides.rubyonrails.org/active_record_validations.html#uniqueness.

This article, in English, explains more how this can occur.

Besides, I always use NOT NULL in the database beyond the validation of presence. I see no reason why not use.

Usually I don’t use foreign keys, but if you really care about data integrity, I suggest using them as well.

Browser other questions tagged sql database relational-model

You are not signed in. Login or sign up in order to post.