Well its an interesting subject, yes it is RDBMS i.e. relational database, tables=relations, alas it does not automatically mean that just because you have a relational database you must use FK. Most of relational databases theories were created at the time when databases were few megabytes in size so the theory start quickly collapsing with terabyte size databases and larger. I would say it is truly depends on your strategy - I've seen companies remove all FK and simplify databases management and vise versa. First and foremost I would test just how many FK violations are really there - if you have FK violations into thousands per 24 hrs you might need to take another look at your app on the other hand if you have very few FK violations does it make sense to lace your database with FK's thus significantly complicating administration? All and all the databases are designed to store your data and that is it, yes of course you can use the database to QA bad coding practices but it does not make it scale very well.
Ask HN: Do you use foreign keys in relational databases?
171–180 of 251 posts
Re: Ask HN: Do you use foreign keys in relational databases?
#172Earlier quoted context omitted.
Eh, there are a lot of people who don't like using FK constraints, calling them all idiots is just bad faith and ignores the reasons they did it. Just because you can enforce a constraint at a specific layer doesn't mean you have to. DB people love shoving all sorts of application logic into the DB and there are good arguments to do it as well as downsides. App people sometimes prefer to do everything in the app and…
If you're using an RDBMS and not using FK or other relational constraints, how do you plan to maintain referential integrity?
I'm not arguing one way or the other wrt to FK, I generally use them.
You can maintain the integrity through code though.
Re: Ask HN: Do you use foreign keys in relational databases?
#173I'm working on a database currently that has "foreign keys", they are the relational fields but there's no foreign key relationship setup in the RDBMS. Also, there's multiple relations on one table instead of just 1-2, so it's a bit like everyone has their hands in the cookie jar. It's a pain in the ass.
Re: Ask HN: Do you use foreign keys in relational databases?
#174No, especially on large tables with billions of records. They make online schema changes impossible. More details: https://github.com/github/gh-ost/issues/331#issuecomment-266...
Curious why no one else had mentioned this. Are they using online schema transforms besides gh-ost and Percona? Does Postgres handle this better than MySQL?
Re: Ask HN: Do you use foreign keys in relational databases?
#175Earlier quoted context omitted.
Don't have a use case for a field being NULL instead of "". Say, if I want to check how many records I don't have value for "ref", I don't want the count(*) query to show count(*) ref 12000 (null) 17030 "" I want both added together. That's for example one simple reason out of many others.
And how do you differentiate between an absence of value and empty value? it sounds like you're just using the database wrong.
That's the fundamental argument supporting the use of NULL.
Extending this one will require you to use "infinite types of NULLs". Decades ago there was an article written explaining it, and that turned into a meme for a while.
For example : Guess you set up a "collector database" that collects data from other databases. It might don't know what the other database's field value is, (call this situation "NULL", the classic case). Or know the other database field value and it's NULL on the other database (now call it NULL-KNOWN, or NULL-TYPE2).
And then do the same thing for a program that now reads from this "collector database ". NULL = Program haven't read the database yet and doesn't know. NULL1 = Program have read the database and it's null. NULL2 = it was null from where this collector database read this data.
See where's that going?
Use a separate field if you want to distinguish.
Re: Ask HN: Do you use foreign keys in relational databases?
#176Earlier quoted context omitted.
ORM is a very valuable tool and should be aggressively used. One can always step down to SQL as needed but otherwise, the ORM logic is easier to write and maintain.
the all-or-nothing approach is prevalent in both camps. i’ve worked in places where a straightforward optimization could not be implemented because it would require the developers to break from the orm-only standard they’d set. i’ve also worked places where orm were held as such anathema that any orm proposal was dismissed out of hand without any sort of discussion.
The all-or-nothing approach is what makes people hate ORMs and go anti-ORM.
Re: Ask HN: Do you use foreign keys in relational databases?
#177Earlier quoted context omitted.
ORM is a very valuable tool and should be aggressively used. One can always step down to SQL as needed but otherwise, the ORM logic is easier to write and maintain.
ORM logic is not easier to write and maintain unless you're bad at SQL and writing your queries without any tool support.
Strongly agreed.
In my experience all using an ORM accomplishes is making sure the people on your team who are amazing with SQL write just as bad queries as those who suck at SQL.
Re: Ask HN: Do you use foreign keys in relational databases?
#178Fear of RDBMSes is quite common. I used to suffer from it too. It’s just so annoying to have to switch your brain to a different programming paradigm every time you need to do something with the database that you start to make up all sorts of excuses as to why it’s really just better to “do it in the code”. Your coworkers argument about FKs making data migrations difficult is one of them. Another classic is the “join…
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.
Did you consider making a 1-1 relationship on a new table that only had the FULLTEXT column? Curious how you evaluated the trade offs
Re: Ask HN: Do you use foreign keys in relational databases?
#179I agree with your colleague, and I insist on pushing my car everywhere because I fear gas as it is flammable. In other words, the world is full of idiots; and any time I start forgetting about it, I read something like your post and I get a wake-up call. What does R stand for in RDBMS is you don't use foreign keys and joins? Please, keep using your FKs, stay safe and don't mingle too much with idiots.
I think the author is talking about 'foreign key constraints' - You could have foreign keys without enforcing a constraint. Personally, I don't use foreign key constraints because: 1. It makes schema migrations and other data-management operations more difficult. 2. On insertion, the database needs to perform an additional check to verify that the record exists at the foreign key; this carries a performance cost; IMO…
> I prefer to assemble data on the front end as much as possible because it allows my REST API calls to be granular
It's clear you have never work with a lot of data.
> The real reason people use joins is because they want to pack a lot of details onto the user's screen
I hate this illusion that web programming is the whole of software development.
Re: Ask HN: Do you use foreign keys in relational databases?
#180I agree with your colleague, and I insist on pushing my car everywhere because I fear gas as it is flammable. In other words, the world is full of idiots; and any time I start forgetting about it, I read something like your post and I get a wake-up call. What does R stand for in RDBMS is you don't use foreign keys and joins? Please, keep using your FKs, stay safe and don't mingle too much with idiots.
Eh, there are a lot of people who don't like using FK constraints, calling them all idiots is just bad faith and ignores the reasons they did it. Just because you can enforce a constraint at a specific layer doesn't mean you have to. DB people love shoving all sorts of application logic into the DB and there are good arguments to do it as well as downsides. App people sometimes prefer to do everything in the app and…
> DB people love shoving all sorts of application logic
I agree that application logic goes into the application, but data integrity is NOT application logic.