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

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

news.ycombinator.com

Post reply on HN
Print view View original

211–220 of 251 posts

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

#211

Post by mkeedlinger »

pif wrote: post #143

I 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 agree that using foreign key constraints is the right choice, but the tone of your comment comes off as very condescending and dismissive, and I don't like it.

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

#212

Post by boatsie »

AdamN wrote: post #125
sonthonax wrote: post #108

Earlier quoted context omitted.

> Another classic is the “joins are slow” argument The only person I knew who died on that hill would insist on doing two queries to the database, and then would insist on doing a client side cartesian join.

I remember getting beers with somebody in the aughts who claimed that he saw an entire website where the url was the key and the webpage was the value in an Oracle database. Any code was SQL operations inside the value field.

Isn’t that effectively what a CMS is?

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

#214

Post by Topgamer7 »

housecarpenter wrote: post #205
Topgamer7 wrote: post #168

Earlier quoted context omitted.

What I'm specifically looking for is a foreign key relation so that you could for example still generate a chart of relations. For example an audit log you wouldn't want the consistency check, but it would still be nice to know it links to X tables.

In the RDBMSes I'm aware of, you can disable foreign key checks while still adding the constraints themselves---they won't do anything as far as the DBMS is concerned, but can presumably still be picked up by whatever tool you are using to generate this link information.

You usually can for maybe a table, or just overall. But I don't think you can specifically for one FK. This is mostly just a small pipe dream I think :')

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

#215

Post by P5fRxh5kUvp2th »

thatjoeoverthr wrote: post #64

Your database is is the state of your system. Guard it! I just ran into severe data corruption at a large client because a programmer four years ago wrote an empty catch block. The system would open a transaction, hit a fault, roll back, then continue writing to the database as if it’s still in the context of the transaction. I spent some time trying to pin down exactly what it did, and found that many writes went th…

a few weeks ago I had a discussion with a developer with 20+ years experience about why you shouldn't squelch DB errors and try to continue running.

Nothing is a hard and fast rule, but in this case the only way for an error to occur is if the query schema differed from the code select statement (column names changed). At that point wtf are you doing trying to keep running without errors, something is fundamentally mismatched between your application and the data.

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

#216

Post by function_seven »

edmundsauto wrote: post #178
function_seven wrote: post #40

Earlier quoted context omitted.

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

I can't remember how much time I spent thinking about it, but if I were to reenact my state of mind at the time, I probably concluded something like, "Without transactions, I'll have to write more code to make sure the ID in both tables stays in sync, and I'll have to send 2 separate INSERTs (sequentially) for every record added, and if the first one fails, I need to handle that, and if the 2nd one fails, I need to handle that differently, and... fuck it. I'll just promise to be good and not use FKs"

Or something. I can't remember the details, but I was (and still am) very averse to complexity in my application code.

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

#217

Post by ajorrell »

Hello frogcoder, Yes, I use FK's for non-trivial systems. Personally, I believe in the enforcement of FK's via DDL as they document the schema and serve their intended purpose of maintaining data integrity according to the rules of the schema. Enforcing constraints and any other system constraint is part of system design. From a debugging perspective, I would rather have the RDBMS return an integrity error when the layers using it don't follow the rules, instead of chasing down where the contraint was not enforced in every layer.

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

#218

Post by skyde »

10x-dev wrote: post #128
sonthonax wrote: post #108

Earlier quoted context omitted.

> Another classic is the “joins are slow” argument The only person I knew who died on that hill would insist on doing two queries to the database, and then would insist on doing a client side cartesian join.

Are joins in a 5NF database now as fast as querying a denormalized database?

they always been faster! When you have 5NF, the database is smaller and all the row you join will be in memory in the SQL server PageCache.

While when using denormalized database, your read will have to go to the disk.

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

#219

Post by pmontra »

masklinn wrote: post #72
pmontra wrote: post #25

Earlier quoted context omitted.

But you could get duplicate key errors when turning fk on. A customer of mine uses UUIDs as keys. That makes almost impossible to get duplicate keys and removes any problem with moving data from one db to another. Reading UUIDs is a minor pain though.

> But you could get duplicate key errors when turning fk on. Why? Fks don’t require unicity. Or do you mean that you target non-unique columns? That sounds… horrendous. But regardless sounds like something you’d want to resolve before declaring the migration finished.

You're right. I meant the troubles we get if we turn off all constraints to ease the migration, end up with duplicate keys in the target table because of mistakes, turn on constraints, errors.

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

#220

Post by jongjong »

P5fRxh5kUvp2th wrote: post #209
jongjong wrote: post #160

Earlier quoted context omitted.

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…

On point 3 it should be noted that it's almost always a mistake to optimize for scale at the start of a projects lifetime. There will be exceptions, but in general this is true. You can always migrate that data to a more useful format if you find it starts hurting you at scale, if you start with the assumption you need the scale you're hurting yourself in the here and now for theoretical future benefit. > The real re…

This perspective only makes sense of you assume that designing a scalable system requires MORE work. My experience is that designing a scalable system requires LESS work if you and your team have the right skillset.

In most cases, I can build a scalable system faster than I can build a non-scalable one with the same feature set.

It would make no sense for me to implement the lesser alternative if it requires the same or more work.

Post reply on HN