How do I maintain referential integrity?

[shreyfirst]

There are no foreign keys, so how do I relate data:

1. Without duplicating data
2. Making sure that every reference still exists
3. Etc

What is the alternative for there being no foreign keys?

[shlomi-noach]

@shreyfirst 👋 good question.

First, taking the opportunity to explain why we don't support foreign key constraints:

1. They way foreign keys are implemented in MySQL stands in the way of Online DDL, and we consider Online DDL, database branching, merging, etc., to be be worth the tadeoff.
2. They also stand in the way of functional sharding/partitioning and of horizontal sharding, both of which are first class citizens in Vitess, the open source engine behind PlanetScale.

Now to answer your specific questions:

Duplicating data

• I'm not sure the existence/absence of foreign key constraints should have effect on data duplication. You will still model your tables & columns in exactly the same way (e.g. a parent table will have an id column, a child's table will have its own id and then a parent_id column to reference the parent table). It's just that the constraint itself won't be there to enforce the connection between the two tables.

• You'd still use 3rd normal form, or 2nd, or whichever normal form you'd like, in exact same way.

• A duplicate can happen whether or not the foreign key constraint exists; you'd use UNIQUE KEY (and obviously PRIMARY KEY) to control uniqueness of data.

Making sure every reference still exists

A bit background before I share my opinion, if you will:

When using foreign key constraints, the user chooses between:

1. on delete no action - prevents deleting a row if a reference exists in child tables
2. on delete set null - sets values in children to null to forcefully break the relationship
3. on delete cascade - delete all children table related rows, and potentially cascade further assuming additional on delete cascade constraints.

Here is my opinion, which I put to practice:

• on delete cascade is extremely dangerous. You intend to delete one row, and you end up deleting hundreds or thousands of rows. Many times that wasn't the intention. Also, performance degrades with excessive locking, and what was meant to be a minimal transaction ends up to be a huge change. I refrain from using "on delete cascade".
• on delete set null practically creates orphan rows. Other than the evident "null" value, this is no different than having no foreign keys at all. It's true that it's easier to later "garbage collect" orphaned rows when they clearly indicate null as opposed to having to join them onto another table, but that's a cleanup implementation detail.
• which leaves us with on delete no action, which is the strongest and most useful behavior in my opinion: the database prevents you from deleting a parent row when children rows still reference it. I think it's the single most important feature FKs provides.
  Now, let's look at the app. The app wants to delete a row, it gets an error. In production you shouldn't normally want to see errors coming from the database. So, before your app deletes that row in the parent table, it must first go ahead and delete the referencing rows from children tables. If those further have FK constraints, they may throw an error. So the app must first clean up the children's children referencing rows.
  As it turns out, it's the app's responsibility to clean the bottom-most tables, and climb its way up until it finally reaches the original parent table. Only then it's able to delete the relevant row.

Now let's consider what happened here. The app had to take ownership of the the entire DELETE stack. Now take away the foreign key constraints: the app still does exactly the same. To make that DELETE happen, the FK constraint wasn't really necessary.

I'd like to clarify I don't mean to downplay foreign keys here. I do want to highlight that it's still very much the app's responsibility to take care of proper and orderly cleanup, with or without foreign keys. The app knows (or should know) your data better than the database; there's a myriad of other constraints the database cannot maintain for you, that may have to do with your specific application logic. It's the app's responsibility to own the data. This is my personal opinion and observation, and it is how I implement apps backed by a relational database.

Etc

I mentioned how a seemingly small transaction can end up being huge. When you don't have foreign keys, you can go in two different ways:

1. Bottom up, as illustrated above. The app deletes the children's children rows, then the children's rows, then the single parent row
2. Top down, asynchronous

Let's elaborate on (2).

The idea is that it OK to have orphaned rows here and there. The simplest reasoning is that when joined to their respective parent tables, the JOIN ends up empty, and it's as if those rows don't exist.

Eventually those rows pile up, though, and you want to reclaim the space. You can then run cleanup scripts (e.g. in the MySQL world you'd use pt-archiver). You would for example

DELETE FROM child_table LEFT JOIN parent_table ON (child_table.parent_id=parent_table.id) WHERE parent_table.id IS NULL

You probably want to only run this a bunch of rows at a time, thus pt-archiver or similar tools.

Common practice is to run those cleanups during low traffic time.

Hope this helps!

[shreyfirst]

dude you know what you're doing

[hhimanshu]

Very detailed answer, totally impressed! I had my strong bias towards having FK constraints, but after reading this, I am opening up for a change! Thank you @shlomi-noach!!

[yasinbhojani]

Great Explanation!

[iperamuna]

Its an nice explanation i was debating with my self wether use planet scale nor because of this FK are not available but, This article helps me take my decision now. Thanks..

[hhimanshu]

BTW, the foreign key constraints are recent additions to PlanetScale. Read more at https://planetscale.com/docs/concepts/foreign-key-constraints#foreign-key-constraints-beta