Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Do you really need foreign keys?

shayon.dev

Post reply on HN
Print view View original

81–90 of 179 posts

Re: Do you really need foreign keys?

#81

Post by binarymax »

pphysch wrote: post #62
binarymax wrote: post #54

How often is data actually DELETEd from production databases? Unless following through with regulatory removal such as GDPR, it’s far better to use an isdeleted flag IMO (so you can un-delete if the action was a mistake).

That's true, but managing the "soft" referential integrity becomes a big authorization/security headache. How do you prevent/allow "deleted" objects from being accessed indirectly? I like Django's ORM approach which allows you to easily set baseline filters for your Model Managers, so you can implicitly exclude "deleted" objects from most queries.

Good point. I’m one of those “never-ORM” people, so writing & maintaining the SQL is prone to error when the DB gets complex, and it does introduce mental overhead during dev.

Re: Do you really need foreign keys?

#82

Post by randomdata »

iLoveOncall wrote: post #25
lrobinovitch wrote: post #14

This github issue is often linked when this topic is discussed: https://github.com/github/gh-ost/issues/331 > Personally, it took me quite a few years to make up my mind about whether foreign keys are good or evil, and for the past 3 years I'm in the unchanging strong opinion that foreign keys should not be used. Main reasons are: > * FKs are in your way to shard your database. Your app is accustomed to rely on FK to…

> FKs are a performance impact. The fact they require indexes is likely fine, since those indexes are needed anyhow. But the lookup made for each insert/delete is an overhead. This is not a valid argument at all and I'm concerned anyone would think it is. If you have a foreign key, it means you have a dependency that needs to be updated or deleted. If that's the case, you will have an overhead anyway, the only questi…

> the only question being whether it's at the DB level or at the application level.

It is not a binary situation like that. With the rise of 'n-tier' systems that are ever so popular today, there are often multiple DB levels. The question is not so much if it should go into the end user application – pretty much everyone will say definitely not there – but at which DB level it should it go in. That is less clear, and where you will get mixed responses.

Re: Do you really need foreign keys?

#83

Post by Pet_Ant »

erik_seaberg wrote: post #27
goostavos wrote: post #24

Follow this advice with caution. Dropping foreign keys is effectively giving up part of the C in ACID. It should be done with very, very open eyes to the downsides. I'm not sure the author is selling the "when" side of this very well. Migrations are "hard" because the database is forcing you to handle correctness criteria that are easy to ignore. "Lock contention" is the database covering your sloppy ill-thought out…

Whenever anyone talks about “the application,” I immediately ask “what about all the other applications?” I promise you that you will find customer service and accounting and biz dev have also built stuff that uses the database to get their jobs done (probably not with the same ORM or even the same language) unless you have taken draconian measures to prevent them.

> probably not with the same ORM or even the same language

I mean, they shouldn't? Like you've just identified a bug: another application can access your database. If another department needs your data, they should request an endpoint that you control. You should be using an "application database"[1] not an "integration database"[2].

[1] https://martinfowler.com/bliki/ApplicationDatabase.html [2] https://martinfowler.com/bliki/IntegrationDatabase.html

Re: Do you really need foreign keys?

#84

Post by Pet_Ant »

mavelikara wrote: post #64
erik_seaberg wrote: post #27

Earlier quoted context omitted.

Whenever anyone talks about “the application,” I immediately ask “what about all the other applications?” I promise you that you will find customer service and accounting and biz dev have also built stuff that uses the database to get their jobs done (probably not with the same ORM or even the same language) unless you have taken draconian measures to prevent them.

+1. When I started in the industry, it was common for more experienced developers to drill the “data outlives the application that generated it” principle into you. Somewhere in the transition to NoSQL and back we lost this.

The _data_ yes, the _database_ no. The data will get migrated to different solutions at different times. I've lost track of how often I've been porting MySQL to SQL Server, SQL Server to Postgres, Postgres to Mongo, Mongo to cloud etc.

EDIT: fixed capitalization of "PostGres"

Re: Do you really need foreign keys?

#85

Post by simonw »

If you work on a large scale system that doesn't include foreign key constraints, try running some queries that should never return results because that would expose data integrity problems.

If your system is hooked up to a data warehouse you can run queries there, too.

I bet you can find all sorts of weird edge-case records doing this.

Re: Do you really need foreign keys?

#87

Post by bjhartin »

endisneigh wrote: post #12

You don’t need foreign keys, no. But you do need referential integrity and foreign keys implemented and enforced by the database is usually the easiest. If you’re disciplined it’s not difficult to implement yourself in the application, but the challenge is if folks can access the database directly. If so, good luck, since it’s inevitable that they end up making modifications and break integrity. If one insists on not…

> but the challenge is if folks can access the database directly

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.

Re: Do you really need foreign keys?

#88

Post by epicfeedback »

Pet_Ant wrote: post #83
erik_seaberg wrote: post #27

Earlier quoted context omitted.

Whenever anyone talks about “the application,” I immediately ask “what about all the other applications?” I promise you that you will find customer service and accounting and biz dev have also built stuff that uses the database to get their jobs done (probably not with the same ORM or even the same language) unless you have taken draconian measures to prevent them.

> probably not with the same ORM or even the same language I mean, they shouldn't? Like you've just identified a bug: another application can access your database. If another department needs your data, they should request an endpoint that you control. You should be using an "application database"[1] not an "integration database"[2]. [1] https://martinfowler.com/bliki/ApplicationDatabase.html [2] https://martinfowler…

They are you. You is they. It’s one company with one goal — keep companying.

The idea that ever “department” should access every other department’s data through some bespoke interface that the latter department maintains might work at some corporate behemoth, but at almost all other scales is absurd.

Re: Do you really need foreign keys?

#89

Post by iamflimflam1 »

Whenever I read posts like this I know, despite all the caveats at the start of the article, a large number of people will take away "we shouldn't have any foreign keys because they impact performance".

I suggest that anyone thinking that has a look at a database where there are no foreign keys.

Re: Do you really need foreign keys?

#90

Post by cogman10 »

dkjaudyeqooe wrote: post #69
cogman10 wrote: post #40

Earlier quoted context omitted.

The main advantage is it's WAY faster if you are writing a lot. 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 millio…

This is your typical false economy trading a very small performance gain for strongly degraded data integrity. 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 superfluo…

> a very small performance gain for strongly degraded data integrity.

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.

Post reply on HN