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

101–110 of 251 posts

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

#101

Post by quickthrower2 »

Foreign keys can be removed during migrations and added back after. You would also disable triggers and check constraints too.

I like foreign keys, check constraints and tight data types. Might as well constrain it and limit the scope for errors.

Application programmers can write some buggy code. The DB should provide a line of defence.

If you don’t have foreign keys that should be a design choice with a legitimate reason the entity can become orphaned.

You need a definition of what that means in real life. E.g PERSONID id 1012 and there is no associated record. This kinda means you need to look at another entity to know what the first entity “means”.

This might be useful for data that needs to sit in distributed databases.

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

#102

Post by BeefWellington »

ivraatiems wrote: post #15

I'm going to assume that by "foreign keys", you mean "foreign key constraints" where the DB itself is insisting on particular relationships. There are a few different schools of thought. I will list them, but the important thing to remember is not to be dogmatic. They are all right or wrong depending on your circumstance. One school of thought says "I want all data in my DB to be normalized. I want it to be right whe…

> Personally, I typically would rather have the application layer do the validation and even the joining of data, a lot of the time, when the application is high-volume. 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.

There is basically no way it is faster for "high-volume" systems to return excess data to the application rather than doing the joins on the dB and returning the record set to the application.

Even if we were talking about multiple billions of records you'd still be better off with a completely denormalized data warehouse style table and doing filtering against indexed columns db-side before sending vast quantities of data to the application.

I think people think this way because of licensing and the specialized nature of DBAs.

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

#103

Post by AdrianB1 »

At work, we use foreign key constraints when it makes sense, that is over 50% of all cases considered. The factors considered are risk and performance: what is the risk of bad data to get in the tables and what is the impact of that happens vs the cost of extra processing time or extra hardware to compensate.

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

#104

Post by pdhborges »

names_are_hard wrote: post #30
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…

I'm not GP so I don't know what they meant, but a key upside to complex logic in the application layer vs the database is that the application layer is often much easier to scale out than the db. Where I work, if I run out of memory in the app I just change a configuration variable and k8s gives me more instances instantly. But if the database is memory constrained and I'm already on the biggest server available to m…

> key upside to complex logic in the application layer vs the database is that the application layer is often much easier to scale out than the db

I think the point of the GP is that all of these application instances are still connected to the DB, doing sub optimal data fetches taxing the database in multiples.

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

#105

Post by Antshockey »

fifticon wrote: post #86
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…

I concur with these. I see colleagues who use EF as 'black magic', who postpone or fear looking into what is happening under the hood. Because they lack insight into what it really does, they regularly cause horrendous queries to happen. My pet peeve with EF LINQ is, that your c# is not really c#, so you may write queries that compile silently, but fail to execute on runtime because the C# cannot be translated to SQL…

Funnily, one of my pet peeves is people worrying about the SQL Generated from EF Linq. If you care that much about it I think you should just be writing the SQL by hand.

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

#106

Post by yuvalr1 »

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

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

#108

Post by sonthonax »

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.

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

#110

Post by ivraatiems »

BeefWellington wrote: post #102
ivraatiems wrote: post #15

I'm going to assume that by "foreign keys", you mean "foreign key constraints" where the DB itself is insisting on particular relationships. There are a few different schools of thought. I will list them, but the important thing to remember is not to be dogmatic. They are all right or wrong depending on your circumstance. One school of thought says "I want all data in my DB to be normalized. I want it to be right whe…

> Personally, I typically would rather have the application layer do the validation and even the joining of data, a lot of the time, when the application is high-volume. 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. There is bas…

We are absolutely talking about billions of records.

But when you say "return excess data to the application" I'm not sure what you mean. Not doing lots of complex JOINs doesn't mean not filtering the queries at the DB at all. Nobody is pulling back a billion records at a time.

Here's an example of what I'm talking about - read the first comment on https://www.brentozar.com/archive/2015/05/do-foreign-keys-ma..., another venue for this same debate.

Post reply on HN