PlanetScale’s foreign key support is a bit of a misnomer; it doesn’t enforce referential integrity at the database level like traditional FK constraints.
Let’s see what that actually looks like. Imagine you have a users table and an orders table, and you want to ensure that every order is associated with a valid user.
-- Schema for users table
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL
);
-- Schema for orders table
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT,
amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id) -- This is the key part
);
In a traditional database like MySQL or PostgreSQL, if you tried to insert an order with a user_id that doesn’t exist in the users table, the database would reject the insert. Similarly, if you tried to delete a user who has existing orders, the database would prevent it (or cascade the delete, depending on your ON DELETE clause).
PlanetScale, however, treats that FOREIGN KEY declaration as metadata. It understands the intent of the relationship, which is crucial for features like schema diffing and generating ER diagrams, but it doesn’t actively block operations that violate it.
So, on PlanetScale, you can insert an order with a user_id of 999 even if there’s no user with id = 999.
-- This will succeed on PlanetScale, but would fail on traditional DBs
INSERT INTO orders (user_id, amount) VALUES (999, 100.00);
This means the responsibility for maintaining referential integrity shifts entirely to your application code. You need to implement checks before performing database operations.
This design choice is a direct consequence of PlanetScale’s architecture, particularly its use of Vitess for sharding and its non-blocking schema changes. Vitess, by design, aims to avoid operations that require cross-shard transactions or complex locking, and enforcing foreign key constraints would fall into that category. By deferring integrity checks to the application, PlanetScale can offer faster schema migrations and a more scalable database.
The primary benefit is the ability to perform non-blocking schema changes. With traditional FKs, altering a table with millions of rows often requires locking the table, which can cause significant downtime. PlanetScale’s approach allows you to add or modify columns and even add FK metadata without impacting application availability.
So, how do you actually work with this? You use the FK declarations for documentation and for PlanetScale’s tooling, but you must write application logic to enforce the relationships. This typically involves:
- Before inserting into the child table: Query the parent table to ensure the foreign key value exists.
- Before deleting from the parent table: Query the child table to ensure no records reference the parent.
- Before updating the foreign key in the child table: Query the parent table to ensure the new value exists.
This might sound like a lot of extra work, but it’s often a pattern that developers adopt anyway for complex applications or when dealing with eventual consistency. PlanetScale just makes it explicit.
The real power comes when you leverage PlanetScale’s schema branching and diffing. When you declare a foreign key, PlanetScale understands it as part of your desired schema. When you propose a schema change, it can compare your current schema (including these FK relationships) with your desired schema and highlight potential issues or inconsistencies before you deploy. It’s a powerful way to visualize and manage your database structure, even if the enforcement is left to you.
Another surprising aspect is how this impacts data loading and cleanup. If you’re migrating data or performing bulk operations, you might find it easier to load data into PlanetScale without strict FK checks, and then run a separate data validation process to identify and fix orphaned records. This can be faster than dealing with constraint violations during the load.
Ultimately, PlanetScale’s approach to foreign keys is about separating schema definition from data integrity enforcement. It prioritizes availability and migration speed, placing the burden of maintaining relationships on the application layer. This requires a disciplined development approach but unlocks significant benefits in terms of operational agility.
The next concept you’ll likely encounter is how PlanetScale handles unique constraints and indexing, and how those also differ from traditional RDBMS implementations.