That's all fine, but we know how this ends up in reality.

Checking that child records that can exist only if there is a parent record are deleted when the parent is deleted and that the foreign key you're using in a record actually exists in the pointed-to table, for the price of a single index you very likely need anyway, is hardly a problem.

You don't need to cascade deletes or anything, that's a red herring, as is migration issues where you can turn off referential integrity while the process is performed.

I only see disadvantages:

- no data integrity check in the database

- more complicated definition of foreign relation, or none at all, leaving possibility for deviant data

- scatteted schema. Cant look at the db table and understand the entire model. Have to hunt in code an potentially across a lot of code.

It just seems like a data integrity issue that needs to be enforced at the db level.

The big disadvantage of foreign keys is they do verify integrity. That means making lookups on every write/update which can be very costly especially as the model becomes more complex.

If you have a read heavy application with low levels of writes then by all means put in foreign keys to you hearts content. But if you are at a point where you have millions or billions of records in multiple tables, they simply aren't feasible.

The overhead of checking for the existence of referred to records in ordinary inserts and updates in application code is unnecessary in most cases, and that is where the problem is. Either you have to check to have any idea what is going on, because your key values are being supplied from an outside source or you should be able write your application so that it does not insert random references into your database.

If you actually need to delete a row that might be referred to, the best thing to do is not to do that, because you will need application level checks to make the reason why you cannot delete something visible in any case. 'Delete failed because the record is referred to somewhere' is usually an inadequate explanation. The application should probably check so that delete isn't even presented as an option in cases like that.

I feel like this belongs to the same strategy as duplicating form-validation on frontend/backend. The frontend validations can't be trusted (they can be skipped over with e.g. curl POST), so backend validation must be done. But you choose duplicate it to the frontend for user-convenience / better reporting / faster feedback loop. The backend remains the source of truth on validations.

The same between database and application; the database is much more likely to be correct when enforcing basic data constraints and referential integrity. The application can do it, its just a lot more awkward because they're also juggling other things and have a higher-level view of the data (and the only real way to check you didn't screw up is to make your testcase do exactly the same thing... but be correct about it -- no one else is going to tell you your dataset got fucked. Also true in an RDBMS, but it's trivial to verify by eye, and there's only one place to check per relationship). Thus in my world-view, the database must validate, and the application can choose to duplicate validation for user-convenience / better reporting. The database remains the source of truth on validations. As an optimization, you remove the database validations, but at your own risk.

And then in a multi-app, single db world, then you really can't trust the application (validations can be skipped), so even that optimization is likely illegal. Or you do many-apps *-> single-api -> db, and maintain the optimization at the cost of pretty much completely dropping the flexibility of having an RDBMS in the first place

At my first job they used mongodb for no reason other than it was the fad at the moment, with the big data and so on.

There were often crashes in the application because our records followed several different schemas, because of bugs in the application that were later fixed, behaviour that got changed, ORM got replaced with hand written code that was much faster…

Basically, what happens with over confident developers and CTO that think they are very good developers and aren't.

(Asking to learn, not to argue)

U could use caching so not to hit db often and also app scales way easier than db.

If the indexes are too much to ask you're basically saying that the referred to foreign records are never looked up (they have no key index!) and/or that the foreign records are never gathered up for referring table. If that's the case the problem isn't having superfluous indexes it's that you have superfluous data in your database.

It's not very small. Doing a lookup on every write can be hugely detrimental to performance especially with large numbers of writes.

> If the indexes are too much to ask you're basically saying that the referred to foreign records are never looked up (they have no key index!)

That does not follow. The lookup for foreign records is not free so avoiding doing it when you don't need to will gain faster performance vs doing it all the time. Indexes make lookups faster, they don't make them free.

Further, you have to consider the impact of locks on such a system. Writes to a table that references another will lock the contents of the second table while the write is in flight. So if I wanted to update the foreign record in any way, that task now gets blocked until data integrity check finishes.

In MSSQL, that lock is held until the end of the transaction.

It is always implemented in the application. The question is about whether you do it in your own application or use someone else's application.

The disadvantage of doing it in your application is that you have to do the work that someone else has probably already done, and are likely not as great of a programmer as that other person, thus more likely to screw it up.

This is not the challenge. The challenge is that you think you can do an 'almost-as-good' job of data integrity as the RDBMS designers.

Even if you could, you will not always be the person that maintains that code.

Of course it's not a real substitute for foreign keys, but definitely better than nothing.