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

131–140 of 251 posts

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

#131

Post by viraptor »

I've joked before that I can often see which tables were created first and which ones were added over a longer time by checking where did people start giving up on foreign keys. But it's kinda true too.

I see them the same as stored procedures. If you use them, you better use them everywhere and have all your data consistency model in the fk and stored procedures. Also, figure out how to disable them during the more interesting online schema migrations. If you can't commit to that, they'll only cause issues down the line.

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

#134

Post by chousuke »

yuvalr1 wrote: post #106
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…

Key statement: integrity of the state is much more important than some inconveniences here and there

I'd go so far as to say that integrity of state is a requirement to build robust software. If your state is ill-defined, it's pretty much impossible to write software that behaves correctly.

Sure, you can guard against some forms of bad data and fix it as it comes in (or abort with errors if your system can handle that), but in order to do anything interesting, you need to make assumptions about the data. the only operation you can perform without assumptions is the identity function, and most software requires much more than that.

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

#135

Post by brightball »

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.

I once had a coworker who dreamed of that exact setup.

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

#136

Post by Diggsey »

vbezhenar wrote: post #120
buro9 wrote: post #59

I use FKs for most things in an RDBMS... but not for all things. For example audit logs get no FKs, when a delete happens the audit logs about the deletion shouldn't be deleted. I always FK a large table (millions or more rows) to a small table (tens to hundreds of rows). But I will pause and ask hard questions about FK a large table to a large table... will this impact migrations? Do I need this FK? Is data integrit…

> For example audit logs get no FKs, when a delete happens the audit logs about the deletion shouldn't be deleted. on delete set null ?

That still modifies the audit log, which is presumably supposed to be immutable. It also means you lose information, eg. if this is a user ID, then before you would be able to determine if two audit actions were done by the same (deleted) user. With "set null", you can't determine that anymore.

The parent's point is that the choice should depdend on the situation. Sometimes a foreign key is appropriate, sometimes it is not.

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

#137

Post by viraptor »

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?

Maybe I'm missing some context, but isn't that true by definition even if the db does nothing special? You either spend time sending N queries and waiting for responses, or join and use one query. Given actually matching scenarios for both, the one with less communication overhead wins.

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

#138

Post by sgjohnson »

habibur wrote: post #130
sgjohnson wrote: post #112

Earlier quoted context omitted.

> I create all fields as NOT NULL and use empty string in place of NULL. ...but why?

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.

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

#139

Post by zupa-hu »

IshKebab wrote: post #27
zergu wrote: post #14

Earlier quoted context omitted.

"Relational" comes from https://en.wikipedia.org/wiki/Relational_model Table = relation

I followed the etymology further because that makes no sense on the face of it. It's referring to the relationship between columns. The table is a mapping from one column to another so there is a relationship between them. This example makes it pretty clear: https://en.wikipedia.org/wiki/Finitary_relation#Example I always thought it was referring to foreign keys too. Pretty bad name in hindsight.

Thanks for elaborating on this! I’ve learned something today.

I couldn’t make up a more misleading name if my life depended on it..

EDIT: Reading the page in more detail, it seems to me relational means both. Indeed each field of a row has a relationship (they belong to the same row after all), but there are other kinds of relationships like one-to-many achieved by foreign keys.

Eg.: each row of a class table corresponds to a class, and a class corresponds to multiple students, so the relationship between the class table and the student table is "one to many"

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

#140

Post by e12e »

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?

Maybe the joins are faster? It's really hard to tell without more context/detail.

I think many were burned by mysql back in the day - trying to use sql as a document database - or using php frameworks that happily did a hundred queries pr page view.

As a general rule of thumb, for a REST app - I'd say the db should be normalized, and the cache layer(s) can handle the denormalization.

Ie when you get /page=1 varnish can spit out a response from ram (which if you squint, is a denormalized projection of your data), or it can go talk to your app, that talks to the db. And the latter is most likely fast enough (tm).

Post reply on HN