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

231–240 of 251 posts

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

#231

Post by goto11 »

radicality wrote: post #184
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…

A lot of it depends on the use case. For example, Facebook - one of the largest (if not the largest) deployments of mysql does not allow any FK constrains. There’s multiple reasons, but one of those is better predictability of db operational perf - a row delete should delete just the row and not potentially trigger N cascading deletes.

Cascading deletes is a separate from FK constraints. You can have FK constraints without cascading deletes.

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

#232

Post by goto11 »

autarch wrote: post #145
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.

The "R" stands for "relations", as in "relations", which is a mathematical concept. SQL calls a "relation" a "table". The "relational" is RDBMS has nothing to do with relationships. But I still agree that OP's colleague is an idiot.

To be really pedantic, tables are relations but a join between two tables are also a relation. Base tables, queries and views are all relations and therefore interchangeable in relational algebra.

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

#233

Post by connormcd »

bawolff wrote: post #74
connormcd wrote: post #58

Earlier quoted context omitted.

If you've got a bit of time to spare, here's a 30min walk through of physical design in the database. https://youtu.be/x0P4zAptTiA In this talk, we start at 10,000 transactions per second, and just by altering the design we get to 20,000 transactions per second... all on a 5 year old laptop. And at no time did we ditch any constraints (primary or foreign). The claim that you can't get performance with constraints on…

I mean, was that 20,000 transactions that are likely to have lock contention related to foreign key constraints? Because otherwise you are measuring the wrong thing.

What kind of lock contention is that? (In Oracle) you'd have to be (a) manipulating the values of a primary key and (b) choosing not to index the child FK column.

Even without (b) I'd be asking "Why are you altering primary keys?" because it pretty much aint a primary key anymore if you're doing that :-)

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

#234

Post by astura »

Topgamer7 wrote: post #214
housecarpenter wrote: post #205

Earlier quoted context omitted.

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 :')

This works in Oracle -

alter table ${table name} disable constraint ${constraint name};

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

#235

Post by hackerfromthefu »

pc86 wrote: post #165
Antshockey wrote: post #105

Earlier quoted context omitted.

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.

I might even go a step farther - you shouldn't care . It's the equivalent of caring whether or not your generated HTML or compiled IL or Assembly "looks nice." If the SQL is performant and it returns the expected data, that is good enough for 99.9% of cases.

The abstraction is great, until it breaks.

In this case, with ORMS, even good ones, this happens often enough in production that to actually master the tool you do need to care.

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

#236

Post by skyde »

goto11 wrote: post #230
10x-dev wrote: post #128

Earlier quoted context omitted.

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

Depends. Denormalized means the database contains redundant data. If a query have to scan 10x or 100x as many rows due to redundant data, it is obviously going to be slower. But it is hard to say anything general since denormalization will make some queries faster and other queries slower.

with good index you will not scan more rows.

But each query will use a different copy of the same data instead of joining with the same copy.

Storing both copy in memory take more space so you can’t cache as much in memory.

I’m not talking redis or memcached but the page cache inside the sql engine.

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

#237

Post by skyde »

lupire wrote: post #199
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.

To be fair thata a reasonable approach if the database is at its monolithic scaling limit in CPU but not IO, while the clients can scale horizontally to more machines. Unlikely in practice, though.

oh so your talking about using the database as a file system and moving all the query logic in the client

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

#238

Post by Izkata »

jve wrote: post #20
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…

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

One example: MySQL table locks. The application can do in parallel what the database can't.

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

#239

Post by jve »

Izkata wrote: post #238
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…

One example: MySQL table locks. The application can do in parallel what the database can't.

Locks ensure you get committed data back and not some data that is in-operation and may or may not end being persisted. If you don't care about it: SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

And SQL can do parallel operations too.

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

#240

Post by P5fRxh5kUvp2th »

jongjong wrote: post #222
P5fRxh5kUvp2th wrote: post #210

Earlier quoted context omitted.

That has nothing to do with data or data modeling.

Well that was just my hobby and side-gig... As part of my day jobs, I also worked on many projects with different databases including MySQL, Postgres, SQLite, MongoDB. I also implemented a side project (a distributed financial transaction processing system) using RethinkDB with per-table sharding and replication which runs on Kubernetes with statefulsets for persistence with automatic deployment and autoscaling and a…

None of that is about data, it's about distributed computing.

No one is saying you're not a smart guy with skills, just that you're obviously not familiar with working with lots of data.

Post reply on HN