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

111–120 of 251 posts

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

#111

Post by astura »

Your colleague doesn't understand relational databases, which, unfortunately, is fairly common - classes on RDBMSes are electives in most CS programs. It's scary/concerning this person is being allowed to operate like this - I'd suggest your company add design reviews and code reviews to reel this sort of rouge behavior in.

...Of course, if nobody understands relational databases at the company, then that won't help...

Even if data migration was difficult with foreign keys for some reason, they can be disabled at the time of migration - no need to put weird constraints on day-to-day operations.

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

#112

Post by sgjohnson »

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?

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

#113

Post by Canada »

I go for constraints every time. Data Integrity is king as long as you can reasonably afford it.

Your data migration aborted on an unexpected error? Well in my view that's a problem and you need to figure out why that is happening and fix it.

But hey, you can always drop the constraint when you decide it's not worth it, and you can always add it back again after. Depends on the application too, sometimes it just doesn't matter if you make a mess.

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

#114

Post by kd5bjo »

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…

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

This is why I avoid ORMs in favor of writing SQL queries manually: I only need to understand one complex system for non-trivial cases instead of two.

(To be fair, I haven’t done any database programming for a few years. ORMs may have significantly improved since I last looked at them.)

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

#115

Post by ivraatiems »

peterhi wrote: post #55
jve wrote: post #20

Earlier quoted context omitted.

> At the volumes my organization works with, it is very difficult to write performant SQL queries that use JOINs and other relationships as a developer - even as a DBA! - and often much easier, for me, to write performant application code. How can this possibly be true? Won't that result in sending unnecessary data over the wire, stressing network and SQL buffer? What are these queries and what are these volumes? I j…

Joins are severely misunderstood and often incorrectly used. I've seen code that had the wrong join and would return 1,000s of rows which then had to be programatically squished down into the data we actually wanted. Some hand crafted SQL usually fixes this Also some ORMs write dreadful SQL where it comes to joins A badly written join (or collection of joins) will take a longer time to run that will, when the system…

You said what I'm trying to say way more clearly than I did.

A lot of people have the same concern but I'm just gonna reply to this comment.

The ratio of SQL-focused devs to non-SQL-focused devs at my org is not favorable. And we certainly DO write joins... just not complex ones. Likewise, we do use constraints... just not all the time against multi-billion-record tables.

But that's not all. Our biggest tables are also our oldest and most unwieldy. Here is an (admittedly outdated in the specifics) example of what it's like to add constraints to a big table in SQL server that didn't have them already: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3eb...

So for a dev to come along and add a whole bunch of FK relationships and/or write some big fun queries against one of these tables is asking for a lot more than one realizes immediately. New devs join and run up against this all the time.

Is it a good or great situation? No. But that's not the question. The question is is it a real one and why.

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

#116

Post by drderidder »

Of course. Foreign keys are elementary. Try recursive CTEs. You can get ridiculous performance gains by not having to marshal data into the application space. It’s all about using the right tool for the job. If you’ve done solid system-level design then running migrations probably shouldn’t be the primary driver of your schema.

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

#117

Post by vbezhenar »

Of course I do. I think that it's crazy not to use database features to increase database integrity. I use foreign keys, I use not nulls, I use checks, I use triggers sometimes.

All my issues with databases usually stem from the fact that someone did not use enough checks and we got dirty data nobody knows what to do with.

My only non-conventional usage of database schemas is text field lengths. I have rule: it's either 20, 200 or 2000. It should be big enough to fit any non-insane value. Like 200 should be enough to fit a phone number. The main point is not to let absurdly broken data in.

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

#118

Post by ljm »

tailspin2019 wrote: post #84
sverhagen wrote: post #80

Earlier quoted context omitted.

Yeah, I was going to say something similar. But ORMs get blamed for obscuring what's going on, to the point that a developer may end up doing some sort of inefficient 1-to-n lookup that would've indeed been much better off as a SQL JOIN. I use JPA/Hibernate professionally, as a decision maker, but I don't think I'm in either camp entirely. ORMs aren't a magic wand, but they do help you standardize the boilerplate tha…

I definitely see that, and ORMs (particularly older ones) have historically made it easy to shoot yourself in the foot. But, everything is an abstraction, and I tend to think that if you use any abstraction, you need to have at least a little bit of knowledge about what’s happening in the layer beneath it. So using an ORM will not be an optimal experience if you don’t know how the underlying RDBMS works. And effectiv…

Agreed, and there's a lot you can gain from an ORM/query builder just in terms of ergonomics or niceness for the 80% use-case.

Doing intensive string manipulation to put your query together becomes painful, fast, especially when you're dealing with optional parts like ordering, limiting, filtering, pagination, etc. It's also incredibly easy to slip in an injection vulnerability as you do that (especially if you're new to programming).

Just don't use it as a crutch because the declarative nature of SQL is vastly more powerful than an imperative wrapper and you'll be at a loss for only knowing the conventions and opinions of your ORM of choice.

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

#119

Post by vbezhenar »

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…

One reason to avoid FK is when your database is partitioned to multiple servers, but that's obvious, I guess, and it's not really RDBMS anymore.

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

#120

Post by vbezhenar »

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

?
Post reply on HN