Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The challenges of supporting foreign key constraints

planetscale.com

Post reply on HN
Print view View original

1–10 of 51 posts

Re: The challenges of supporting foreign key constraints

#4

Post by jtriangle »

shlomi-noach wrote: post #2

Post author here, happy to answer technical questions.

So how do you deal with orphaned child rows when reverting? I assume it's up to your users to deal with them or not? This very much seems like a clever automation for chosing when to care about foreign key constraints and not outright enforcement

Re: The challenges of supporting foreign key constraints

#5

Post by shlomi-noach »

jtriangle wrote: post #4
shlomi-noach wrote: post #2

Post author here, happy to answer technical questions.

So how do you deal with orphaned child rows when reverting? I assume it's up to your users to deal with them or not? This very much seems like a clever automation for chosing when to care about foreign key constraints and not outright enforcement

Yes, you got that right! If you drop a foreign key constraint from a child table, and then follow up to INSERT/DELETE rows on parent and child in such way that is incompatible with foreign key constraints, and then revert, then the child, now again with the foreign key constraint, can have orphaned rows. It's as if you did `SET FOREIGN_KEY_CHECKS=0` and manipulated the data unobstructed. The schema itself remains valid, and some rows do not comply.

It's worth noting that MySQL has no problem with this kind of situation. It never cares about the existence of orphaned rows. It only cares about not letting you creating them in the first place, and it cares about cleaning up. But it doesn't blow up if orphaned rows do exist. They will just become ghosts.

Re: The challenges of supporting foreign key constraints

#7

Post by guptamanan100 »

obviyus wrote: post #6

The Planetscale blog consistently puts out such high quality posts. I can highly recommend following their YouTube channel as well. I’ve learned a ton from the incredibly well made videos Aaron puts out!

Yep! Aaron is great, isn't he!!? I make it a point to watch all the videos he puts out. We appreciate your support!

Re: The challenges of supporting foreign key constraints

#8

Post by padre24 »

guptamanan100 wrote: post #3
shlomi-noach wrote: post #2

Post author here, happy to answer technical questions.

I am the other post-author, and I am available too.

This was a great read, thanks.

Are there any plans to support recursive CTEs? What are the technical challenges there?

Re: The challenges of supporting foreign key constraints

#9

Post by guptamanan100 »

padre24 wrote: post #8
guptamanan100 wrote: post #3

Earlier quoted context omitted.

I am the other post-author, and I am available too.

This was a great read, thanks. Are there any plans to support recursive CTEs? What are the technical challenges there?

Thank you for the compliment!

We recently started adding support for CTEs in Vitess! You can check out https://github.com/vitessio/vitess/pull/14321 if you want to see some technical details of the implementation.

For now, we have added preliminary support by converting them to derived tables internally, but we believe that we need to make CTEs first-class citizens themselves of query planning, specifically because recursive CTEs are very hard to model as derived tables. Once we make that change, we can look towards supporting recursive CTEs.

This however will take some time, but then, all good things do!

Post reply on HN