Foreign key constraints are now generally available
By Taylor Barnett, Rick Branson |
Today, we are announcing that foreign key constraints are generally available on PlanetScale within any unsharded database.
Foreign key constraints can be used to enforce referential integrity in your database. During our beta phase in the last two months, over 2,400 PlanetScale databases have enabled foreign key constraints. You can read more about our foreign key constraints support in the PlanetScale documentation.
Previously, we wrote about how we overcame the technical challenges of supporting foreign key constraints alongside features, such as database branching, non-blocking schema changes with Online DDL, gated deployments, and database imports.
Note
If you want to horizontally scale your database with sharding and need foreign key constraints, reach out to us, and we can chat more about your database requirements and PlanetScale.How to enable foreign key constraints in your database
To enable foreign key constraints in your PlanetScale database, go to your database’s ”Settings” page and check the box to Allow foreign key constraints.
On the database’s ”Dashboard” page, you will see a loading spinner that says it is “Enabling foreign key constraints.” Once it no longer shows, you can use foreign key constraints in your PlanetScale database!
For most cases, foreign key constraints should work as expected in PlanetScale. There are a few cases to be aware of that are unsupported or result in less ideal behavior. You can read more in the limitations section of the foreign key constraints documentation.
If you don’t have an existing PlanetScale database and have an existing internet-accessible MySQL or MariaDB databases that use foreign key constraints, you can also import it into PlanetScale using our database import tool.
Foreign keys versus foreign key constraints
Foreign key constraints are often confused with foreign keys, which PlanetScale has supported since day zero.
Foreign keys are a logical association between tables in a relational database based on the value of two related columns. It enables you to look up related data based on matching values between specific columns between the two tables. For example, given a simple project management schema, a tasks table may contain a column named project_id that can be used to look up the related project based on the id column of the projects table.
A foreign key constraint is a database construct that takes foreign keys a step further and forces the foreign key relationship's integrity (referential integrity). Namely, it ensures that a child table can only reference a parent table when the appropriate row exists in the parent table. This helps keep the related data consistent.
Using the same example as above, if foreign key constraints are not defined and a project is deleted, the database will take no action on any associated tasks. With foreign key constraints configured between projects and tasks, the database engine can perform cascading actions on the related tasks such as deleting them as well, or nulling the project_id field.
While foreign key constraints can help ensure referential integrity, they can cause degraded performance in high concurrency workloads and introduce more complexity in the database. As with any database feature, developers should weigh the advantages and disadvantages of using foreign key constraints for your specific application.
Helpful documentation on foreign key constraints
The following are useful documentation pages for if you choose to or choose not to use foreign key constraints in your PlanetScale database: