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

121–130 of 251 posts

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

#121

Post by astura »

mccorrinall wrote: post #83
kureikain wrote: post #81

I used to scare of it thinking why do I need it? everytime I delete sth it annoying me. I read blog post of GIthub not using FK key, and try to convince myself that FK isn't worth it. One day, I decided let try this out. And turning out it wasy easiser than I think. When delete data just make sure to use cascade or ensure its associated data is deleted, which make sense. Once I embrace it, FK now becomes my friend to…

It’s can become pretty annoying if you want to alter your database schema.

How?

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

#123

Post by spiffytech »

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…

Data that's malformed at captured time is often indistinguishable from outright data loss.

Since it just gets worse the longer you accept corrupted data, this is a good justification for "crash early" programming, like DB enforcement of data integrity.

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

#124

Post by wruza »

fabian2k wrote: post #99
moonchrome wrote: post #75

Earlier quoted context omitted.

My main problem with Entity Framework is the magic underneath. Like simple operation x = Ef.Find(xid) x.Name = "something" y = Ef.Find(xid) what is y.Name ? Even though you didn't save anything to the database yet ? And the second Find didn't actually refresh from the database ? Oh and the random bugs where people improperly include related entities but it somehow ends up working because they are automatically added…

Once you move beyond trivial cases you really need to spend time understanding the principles behind the ORM you're using. They are always a very leaky abstraction, there is not really a way around that. In this case the important part to know is that the DbContext represents the unit of work and "knows" Entities you previously queried on it. That's very useful, but also can hide bugs like you mentioned with the Incl…

They are always a very leaky abstraction, there is not really a way around that.

Editing in general is hard. E.g. if in a form you change a field that participates in some filter which generates a dataset to be used in that form, it creates an issue that a naive join now returns incorrect data (because the join condition itself was edited). Complex ORMs which help with that^ are not leaky abstractions, they just try to avoid mistakes an average programmer would do anyway in “trivial” SQL tasks without blinking once.

And yes, gp question about x vs y means that no thought of editing contexts was ever considered. Plain old fetch-store is too low-level and doesn’t represent a model that business logic thinks in.

^ Idk about EF in particular, just assuming

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

#125

Post by AdamN »

sonthonax wrote: post #108
mtts wrote: post #31

Fear 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…

> 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.

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

#126

Post by AdamN »

Semaphor wrote: post #57
mtts wrote: post #31

Fear 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…

> Much better than ORMs I recently migrated to EntityFramework Core (from the non-core version) and I’m actually impressed. Most SQL is pretty much what I’d write by hand. Now granted, if there are complex joins, subqueries and stuff, I don’t even try wrangling the ORM to somehow give me that output, but still. I feel more comfortable just using EF than I used to.

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.

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

#128

Post by 10x-dev »

sonthonax wrote: post #108
mtts wrote: post #31

Fear 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…

> 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?

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

#129

Post by pards »

> difficulties during data migrations

It's not difficult; it merely requires that you think about the order of the migration operations.

> He'd rather have a smooth data migration process than having an unexpected error

This should be grounds for dismissal. Data integrity problems should be dealt with immediately instead of making it someone else's problem later. He's just kicking that "unexpected error" down the road to some poor soul that will spend weeks trying to figure out how the data got so messed up.

If you don't care about data integrity then use a different data storage solution.

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

#130

Post by habibur »

sgjohnson wrote: post #112
habibur wrote: post #35

I don't use foreign key constraints on the database. I create all fields as NOT NULL and use empty string in place of NULL. Last time I tried foreign key constraints can't work in an environment like this.

> 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.
Post reply on HN