Do I really need to define constraints in the database?

14

Ruby on Rails seems to have been thought of since it is not necessary to define constraints (like Foreign Keys ) in the database, / p>

Normally the only constraint that is created in this case is the primary key of the id column, otherwise it's just indexes (not necessarily as unique) for performance reasons. I also do not use NOT NULL , for example.

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

Should I be concerned about integrity risks and the like when working in this way (leaving everything in charge of the application)?

    
asked by anonymous 13.05.2014 / 20:30

4 answers

14

On a production server, I would not rely solely on the application to ensure the integrity of the data. The function of foreign keys is exactly to guarantee the relation between the tables of the Bank.

Assuming that a bug in the relationship of two application models is only noticed after some time on the production server, the time for benchmarking will be much longer than the time to correctly implement key relationships.

I'm currently developing on a small team, where information reliability is more important than the application itself. Soon the creation of the foreign keys, defining the fields as NOT NULL and a good normalization in the bank is for us the first step.

We use Laravel here, a framework in PHP that uses an ORM very similar to the Active Record of Rails. Defining a well-structured database helps us to see relationship errors in the application right from the start, preventing multiple bugs from coming into production.

    
13.05.2014 / 21:38
9

No need to use them. But if you must, it's an issue that depends on each scenario.

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

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

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

    
13.05.2014 / 21:54
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'd 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 (eg, processing unstructured data of the type used in Big Data ), or with a short service life (users are not expected to use the system extensively) some other cases go well with NoSQL.

Finally, when using a relational DB without taking advantage of what it has to offer you end up underusing its features and running out of the benefits it could get.

    
14.05.2014 / 19:45
4

Rails only does not guarantee data integrity. In particular, the validation of uniqueness does not guarantee that duplicate records are not created in case of concurrent access. Rails Guides itself mentions this in link .

This article explains how this can happen.

Furthermore, I always use NOT NULL in the database in addition to presence validation. I do not see any reason to use it.

Generally I do not use foreign keys, but if you really care about data integrity, I suggest using it as well.

    
21.05.2014 / 14:16