So Referential integrity is good, but are there scenarios where not having constraints is better?
For example when all the logic of relationships is kept in code? or when the database upgrade is simplified?
PS: I am not asking it is good not to have referential integrity, just that what scenarios would justify it
There are several instances in which the performance and/or reliability of a system can be improved by loosening or eliminating referential integrity constraints:
Data warehouses and ad-hoc reporting databases. These may or may not need to deal with inconsistent data as another answer mentions. However, even if the data is consistent, it is usually much more efficient and reliable to import one table at a time, rather than one "entity root" at a time. Referential constraints can needlessly complicate matters when the business requirements for a decision-support database do not actually require real-time consistency.
Any systems with strict privacy rules, or systems which must support hard-deletion of master records for some other reason. You'll often need to support the deletion of all personally-identifiable customer information, but deleting all their transactions along with that customer will actually cause inconsistency from the business point of view, e.g. in accounting reconciliation. In these cases you generally want to keep that user ID - the fact that the user does not "exist" means the account was deleted. Of course, you could come up with a process wherein you replace all the user data with a stub, but it's a lot of extra work for no real benefit. Note that this scenario justifies removing some relational constraints but doesn't mean you should have none at all.
Complex hierarchies of data can incur serious performance hits with database-enforced referential integrity. Worse, since most database engines try to estimate an execution plan based on table statistics, this can result in deletions (or even some updates) taking several minutes to complete when there are only a couple of rows actually affected! DRI works best for master-detail type situations and maybe some 3-level hierarchies with very small tables (say a few million rows).
Very large tables in the billions of rows. DRI implies an index or constraint on the foreign key column, and if you don't actually need to query by this key, then the index can result in an enormous amount of wasted space - especially when you multiply by several constraints or several tables.
CQRS (Command-Query Responsibility Segregation) and/or event sourcing eliminate a lot of the traditional "future-proofing" benefit associated with DRI. This is similar but not identical to the point above. For example, let's say you've designed a billing system with the traditional order and order-detail tables. The order detail has a product ID and you actually do need to perform analysis on this - for example, correlating product purchases for use in a "customers who bought this also bought..." recommendation engine. However, in this case your architecture dictates that not only shouldn't you query the actual order detail table for this information, but you shouldn't query it at all - these are event tables that trigger downstream processes which create additional views of the data suitable for specific queries and reports. Having the indexes and referential constraints is a waste of resources because it doesn't guarantee anything of value - it won't help keep your query models in sync, but it will add significant overhead to the insertions, which is exactly what you need to optimize in a CQRS/event-sourcing architecture.
If your database needs to be partitioned (or better yet, distributed horizontally) for either performance or availability, you usually can't make any guarantees that the master and detail rows will always reside in the same "shard" - especially if there's a complex or even recursive set of relationships. It's just too hard a problem to solve, and if you could solve it, it might kill your entire partitioning strategy (e.g. billions of detail rows all reference the same master row 3 levels up in the hierarchy and therefore can't be partitioned...). That's part of the reason why there are a bunch of confusing rules concerning what combinations of partitioning, partition operations, and PKs/FKs are allowed. Distributed databases really don't play nice with referential integrity, which is why most nosql databases don't support it (i.e. because they are meant to be distributed).
This is just a few of the reasons off the top of my head... I'm sure I could come up with more, given time.
It's worth noting that all of these involve conscious decisions based on system architecture and probably considerable subject-matter expertise in your business domain. Laziness or ignorance aren't very good reasons, nor is the simplistic view of "it's faster that way!". It's faster until your data becomes inconsistent, at which point you'll end up sinking far more money into system maintenance than you would have originally invested into some higher-end hardware. It's really important that you have consistency and concurrency models in place; most systems that I've been exposed to which don't explicitly use DRI are using some form of optimistic concurrency and eventual consistency guaranteed by back-end durable messaging systems - the notable exception being data warehouses which are usually driven by ETLs instead.
The main reason would be for dealing with inconsistent data.
Many systems import data from outside and have to deal with failures and exceptions. Imagine you're importing a daily sales log from a retail shop. Inevitably the shop will need to sell something that's not in the inventory, or is mis-quantified (they sell something even though quantity on hand is zero in the database). Depending on your foreign keys either or both of those may cause a violation.
I've fixed a system like this that used key enforcement, and it was very difficult for the staff to cope with it. The import would fail if the inventory records for the shop didn't exactly match the ones in the warehouse, and I implemented a whole set of rules and manual intervention options so that staff could manually cross-link items, add items on the fly, and just say "create a new inventory item for anything that doesn't have one, and I'll fix it later". That creates its own havoc, but it's not always possible to stop the world while you sort out the data.
(edit) Worse are systems I've seen staff fighting where they can't put product through the register if it's not in the system. You walk up to the counter carrying an item that has a price tag on it, they scan it but look at you helplessly because "computer says no". Retail is all about selling what you have, and your job as a programmer is to facilitate that. So one way or another there will be a legitimate business requirement to sell non-inventory items. Supermarkets often have a "manager product" which lets selected staff type in a description and a price, either on the register at point of sale or to print a special price sticker. One construction equipment "retailer" that I did some work for had "power cords, used" as an item in their inventory... and had sold quite a few of them over the years.
This applies when dealing with external systems of any sort. You need to either have a fairly loose way of matching, create necessary primary key values on demand, or reject records, or some combination of those. And good duplicate detection.
Speed.
If you're inserting a whole heap of data, the DB doesn't need to spend its time creating indexes.
Having said that don't do this. Create the foreign keys. Never rely on application logic :)
Whether you keep the relation in the database or in code, it's still a relation, and since the database can do it better, you put it there.
The database is the central repository for not only the data, but also the schema. Put it anywhere else, and you run the risk of one copy of the frontend program having a different schema than the others.
Kaboom.
