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

61–70 of 179 posts

Re: Do you really need foreign keys?

#61

Post by eddd-ddde »

goostavos wrote: post #32
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.

I would argue that if you have completely different contexts / business lines / concerns (customer service, accounting, biz dev) all directly accessing the same database you have far, far larger architectural concerns that FK will ever hope to address.

I strongly believe the opposite. Your business IS your database. How do you handle access to the data without access to the data?

It is inevitable that 2 "organizations" will at some point need access to the same data. You could just have each of them decide how they interact with it. It doesn't matter how they do it because the database itself makes sure invariants are kept true (such as FKs).

What's the alternative?

Re: Do you really need foreign keys?

#62

Post by pphysch »

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.

Re: Do you really need foreign keys?

#63

Post by endisneigh »

mavelikara wrote: post #57
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…

But you pay the cost in checking it in the application, as GP said. If so, it simply is moving the cost from db to application layer. Is there a reason the checks can be implemented more efficiently in the application than the DB can?

The reason is because the application knows what’s actually happening with the data.

Re: Do you really need foreign keys?

#64

Post by mavelikara »

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.

+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.

Re: Do you really need foreign keys?

#65

Post by eddd-ddde »

x86_64Ubuntu wrote: post #17
KeyBoardG wrote: post #2

Unless your project is small and for learning, or won't be around for very long, Yes. You absolutely do or will end up in a technical debt world of hurt. I have worked on systems without them that are 15+ years old and the vast, vast majority of fixes and refactoring were self inflicted wounds like this. We added FK's and indexes where necessary and wound up faster and safer.

That sounds awful. My first instinct would be to see where an FK constraint would fail, and then ask the business what it should be. There is no guarantee that every business case from way back needed an FK, or that anyone in the business knows which FK value should be there. It sounds absolutely miserable.

In my experince, adding proper constraints will not only ensure code can never write invalid data to disk, it will also help you realize when business rules are lacking or failed to consider edge cases.

Re: Do you really need foreign keys?

#66

Post by arrowleaf »

eddd-ddde wrote: post #61
goostavos wrote: post #32

Earlier quoted context omitted.

I would argue that if you have completely different contexts / business lines / concerns (customer service, accounting, biz dev) all directly accessing the same database you have far, far larger architectural concerns that FK will ever hope to address.

I strongly believe the opposite. Your business IS your database. How do you handle access to the data without access to the data? It is inevitable that 2 "organizations" will at some point need access to the same data. You could just have each of them decide how they interact with it. It doesn't matter how they do it because the database itself makes sure invariants are kept true (such as FKs). What's the alternative…

Have a single application clearly owning the data. All access to the data is done through that application. Why would you _want_ multiple applications deciding how to handle data in a common store? What about access authorization to sensitive data?

Re: Do you really need foreign keys?

#67

Post by out_of_protocol »

shayonj wrote: post #18
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…

That is great and I noticed it’s something I have always assumed should be the case - no indirect access to the database outside your application / ORM. Because then you open a can of worms.

Doesn't help much though. It only takes not wrapping related inserts into transaction in one place in your code. i know zero frameworks/ORMs that can help you with that

Re: Do you really need foreign keys?

#68

Post by arp242 »

ww520 wrote: post #41

The performance gain by dropping foreign keys doesn’t hold water. You still have to do the referential checks in application or in the ORM code. Unless you meant dropping referential checks.

Here' the common scenario:

  obj = new_object()
  obj.col1 = get_value_from_somewhere()
  obj.user_id = get_logged_in_user_id()
  obj.insert()
Or:

  # Find correct ID.
  obj.some_id = run_query("select some_id from tbl where x=?", param)
Lots of variation on that, but the user_id and some_id here are pretty much guaranteed to be accurate when implemented correctly. The biggest potential issue might be race conditions with deletes on the parent ID, but just having soft deletes sufficiently alleviates that (potential) small issue.

Re: Do you really need foreign keys?

#69

Post by dkjaudyeqooe »

cogman10 wrote: post #40
nonethewiser wrote: post #34

Earlier quoted context omitted.

What would be the advantage of implementing it in the application? 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 th…

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 superfluous data in your database.

Re: Do you really need foreign keys?

#70

Post by LtWorf »

endisneigh wrote: post #63
mavelikara wrote: post #57

Earlier quoted context omitted.

But you pay the cost in checking it in the application, as GP said. If so, it simply is moving the cost from db to application layer. Is there a reason the checks can be implemented more efficiently in the application than the DB can?

The reason is because the application knows what’s actually happening with the data.

It's supposed to know, but it actually doesn't.

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.

Post reply on HN