Ask HN: Do you use foreign keys in relational databases?

This:

"You can always rewrite software. Rewriting bad data is not only difficult but often impossible."

I think that this would make a good HN poll, for example: https://news.ycombinator.com/item?id=21231804

Though the answers would probably vary and there's most likely lots of nuance per individual case (which might matter more than just yes/no), personally I can think of the following as examples:

  - yes, we use foreign keys
  - yes, but we use them in testing environments and turn them off in prod
  - no, we don't use them because our database doesn't support them (e.g. distributed like TiDB)
  - no, we don't use them and check integrity and orphaned data ourselves
  - no, because our system design doesn't allow us to use them meaningfully (e.g. OTLT and EAV)
  (also, talking about whether to cascade or not might be useful, e.g. whether you want to manually clean up related data, or not)

Someone else mentioned varying schools of thought, which rings true. Personally, my opinions about database design in general are along the lines of:

  - avoid EAV and OTLT outside of very specific cases, have multiple tables over few (e.g. employees, employee_contact_information, employee_vacations, employee_notes instead of employee_fields and/or employee_field_values)
  - have foreign key constraints across your tables, so that you might not end up with orphaned data, *consider* cascading the constraints (depends on requirements)
  - use views liberally, especially for complex requirements in regards to selecting data, so that your app (or ORM in it) can map against it in a simple manner
  - outside of batch operations, prefer to modify data through the app, instead of procedural SQL, since that's easier to debug; I'm yet to see someone use breakpoints/watches for stored procedures successfully

Though my ideal database design probably looks way different and scales slightly differently (which hasn't mattered as much yet) than someone else's.

There are people who want to build their entire database around a "classifier" system, about which I wrote previously here: https://news.ycombinator.com/item?id=32416093 (this also makes the DB hard to visualize as ER diagram because of meaningless links, and sometimes makes the DB hard to use without the app, e.g. type_enum_value vs table_name).

There are people who want to do everything in procedural SQL (I've seen application views call stored procedures to fetch all data and validate forms), there are those who don't want to touch it with a 10 foot pole.

It really varies a lot, though in my experience it's invaluable to be able to feed a database into something like DbVisualizer and get an overview about how the different tables are related to one another, basically like documentation: https://www.dbvis.com/

Foreign key constraints are easy to remove if they become a problem but almost impossible to add once data integrity problems arise (and I've never seen them not arise in projects without FK constraints).

The small number of people with high enough scale that they can't use them know who they are, the rest of us need to think carefully when performing database migrations and reason out the order of operations required to maintain data integrity (sounds like a good idea anyway?).

We used to do large setups at companies for what was then called intra and extranets begin 00s. These were very read/write intensive as the staff and partner staff would be on there basically all the time during office hours and data was not great for caching as data changed a lot especially in some companies like large hospitals and universities. We used mysql (I cannot remember why) and we did a lot of performance testing at that time; we removed all joins which made everything a lot faster. This is no longer the case now but indeed many people still believe it ; not (only) because they saw or tried it back then, but also because it’s less strain on the brain to just do single table selects and use not FKs or joins.

Back in the day I was forced to ditch FKs in my MySQL application, because I needed a FULLTEXT index on one of my columns, and MySQL only supported that type of index on MyISAM tables (this was on 5.x or something). MyISAM didn't do foreign keys.

It was a pretty central table, and the inability to use FKs there kinda spread outward.