We do not use foreign keys (2016) • Hacker News Board

Re: We do not use foreign keys (2016)

#201

Post by papln » Fri, Nov 08, 2019, 11:23 PM UTC

brlewis wrote: post #86

Hamuko wrote: post #61

Earlier quoted context omitted.

I'm guessing it's the possibility for data loss. So someone accidentally deletes a user and it cascade deletes all invoices referenced to that user (deleting invoices is a big no-no in accounting).

If the author is thinking of that kind of situation, he's right that ON DELETE CASCADE would be inappropriate, but he's also incredibly wrong to not want a foreign key enforcing that relationship. Deleting a user associated with invoices that should not be deleted is a big no-no too. A foreign key would not let the user be deleted until after the associated invoices are deleted. There are plenty of other situations w…

the problem with ON DELETE CASCADE isn't the CASCADE, it's the DELETE. Almost always, you want to to mark an entity disbled, not really DELETE it. If you want to DELETE it (for GDPR?) you should have something in place to fail your delete unless you've properly defined how to clean up danglig keys. (Perhaps what you need to do is delete the non-primary-key fields containing user data, but keep the row for relational integrity)

ON CASCADE DELETE is used for deleting logical sub-components (a row owned by another row) in a well normalized database -- it's for deleting your dadress book when you delete your account. It's not for deleting all your friends when your account is deleted.

Re: We do not use foreign keys (2016)

#202

Post by barrkel » Fri, Nov 08, 2019, 11:23 PM UTC

Enable FKs for test and development, disable them in production, for tables that see billions of rows.

FKs have other downsides. They add locks on the referenced rows, to ensure they don't disappear before the transaction commits. If you start out with 32-bit primary keys, you have a major pain when you go over 2 billion records - incrementally upgrading all your FKs before your PK breaks your referential integrity, since FKs generally need to have the same type on each side.

Re: We do not use foreign keys (2016)

#203

Post by papln » Fri, Nov 08, 2019, 11:24 PM UTC

dekhn wrote: post #96

topmonk wrote: post #88

Earlier quoted context omitted.

> So any time you want to get all the CHILDREN of a PARENT you have to query all children to see if they have a FK to the appropriate PARENT. It's just an index lookup, so what? You'd rather mash all the data into a single parent field, is that it?

i will admit that once I saw postgres has arrays and composite types, it seemed more natural to model this as NODE with array of CHILDREN. I think it's more about the expressivity of pointers and arrays compared to the "backwardness-feeling" of index lookups.

Huh? If you have a tree with parent pointing to children, how do you get a child's parent? You need an index. Same as with a DB.

Re: We do not use foreign keys (2016)

#204

Post by cryptonector » Fri, Nov 08, 2019, 11:24 PM UTC

kenshaw wrote: post #188

Foreign keys should definitely be used in most schema designs. While I can agree that they are problematic when doing a schema migration, a schema migration would only happen "very rarely" whereas inserts/deletes happen regularly. I believe this comment is more of a reflection of the specific database (MySQL) which has relatively poor FK performance and adjunct issues when compared to other SQL databases. Also, from…

2b) And keep your schema changes as incremental and backwards-compatible as possible. Add new tables, add columns to tables, but don't drop or rename anything. If you need a flag day, in PG terms just setup a new schema, use triggers to sync the two, and migrate your apps to use the new schema -- when they're all migrated, drop the sync triggers and the old schema.

Re: We do not use foreign keys (2016)

#205

Post by barrkel » Fri, Nov 08, 2019, 11:24 PM UTC

spookthesunset wrote: post #46

davidw wrote: post #11

How about this: if your service gets as big as github, then maybe consider doing odd things to eke out more performance or shard or whatever. Otherwise: use FK's to maintain stronger data integrity. As hesk mentions below, in Postgres, you can do all kinds of table ALTERing if needs be.

FK’s don’t just maintain “stronger” data integrity, they are the only way to maintain relational data integrity. Application code cannot maintain that relational integrity, period. Any developer who thinks application code can enforce relational integrity is naive and does not understand relational database systems. It is impossible for any layer above the database itself to keep things from getting corrupt.

You can implement FKs with the same cost as FKs. Look up the referenced rows and add read locks. It's not cheap. FKs are by no means free.

Re: We do not use foreign keys (2016)

#206

Post by kraig911 » Fri, Nov 08, 2019, 11:25 PM UTC

conroy wrote: post #5

When posts like these come up, I'd like to remind people that context matters when making technical decisions. What works for large companies with huge scale (GitHub, Google, Facebook) may not work for you. As a counter point to the linked issue, I operate a few small applications. Foreign-keys (and constraints in general) are great at ensuring that invalid data doesn't find its way into your database. Yes, they have…

+1 million times this. Seems like everywhere I work with are concerned about google size data problems when the apps I'm working on are at best 1000 concurrent users...

Re: We do not use foreign keys (2016)

#207

Post by cryptonector » Fri, Nov 08, 2019, 11:25 PM UTC

dekhn wrote: post #65

I have been perpetually annoyed at the SQL/RDBMS/relational calculus model. It always feels like a huge context shift from imperative programming. after many years of writing SQL, I noticed that many other people end up writing SQL statements that look more or less like computer programs (CASE statements, subselects, etc). It all came to a head when I naively asked an experienced SQL developer how to represent a tree…

All you need to know is WITH RECURSIVE CTEs.

Re: We do not use foreign keys (2016)

#208

Post by cryptonector » Fri, Nov 08, 2019, 11:26 PM UTC

camnora wrote: post #103

dekhn wrote: post #65

I have been perpetually annoyed at the SQL/RDBMS/relational calculus model. It always feels like a huge context shift from imperative programming. after many years of writing SQL, I noticed that many other people end up writing SQL statements that look more or less like computer programs (CASE statements, subselects, etc). It all came to a head when I naively asked an experienced SQL developer how to represent a tree…

An alternative is using a nested set https://en.m.wikipedia.org/wiki/Nested_set_model

No. Just use an RDBMS that doesn't suck -- one that supports WITH RECURSIVE CTEs.

Re: We do not use foreign keys (2016)

#209

Post by [deleted] » Fri, Nov 08, 2019, 11:27 PM UTC

taurath wrote: post #111

corebit wrote: post #71

Earlier quoted context omitted.

The biggest issue with CQRS I've seen is people thinking CQRS means you need multiple, duplicate data structures, mappers, a few Kafka topics and a PhD, when IN REALITY all it means is you put methods that return data without modifying it in one interface/class and methods that have side effects in another interface/class - which is really just a good application of interface segregation. Moreover, you now have a gre…

To be clear, I'm referring to the (in my circles) usual parlance of not "pure" CQRS, which is as you describe and could entirely be in a OOP "Model" layer, but what usually goes with it - things like event sourcing for auditing, cached read services (eventual consistency), etc. Should've been more specific but was just pattering off the first big complexity thing that came into my mind.

[deleted]

Re: We do not use foreign keys (2016)

#210

Post by barrkel » Fri, Nov 08, 2019, 11:27 PM UTC

davidw wrote: post #11

How about this: if your service gets as big as github, then maybe consider doing odd things to eke out more performance or shard or whatever. Otherwise: use FK's to maintain stronger data integrity. As hesk mentions below, in Postgres, you can do all kinds of table ALTERing if needs be.

You don't need a service as big as GitHub; you just need billions of rows.

I think you should enable FKs for test and dev, and possibly QA, but disable them in production.

Depending on your database, you'll get surprising interference in concurrent operations via other means than FKs, but FKs don't help.