Vitess can emulate foreign key constraints, but it does so by pushing the constraint enforcement down to the MySQL instances. This means that Vitess doesn’t actually "manage" the foreign keys in the way a traditional RDBMS might; instead, it relies on MySQL’s native foreign key capabilities.
Let’s see how this works in practice with a simple example.
Imagine we have two tables, users and orders, where each order must belong to a valid user.
CREATE TABLE users (
user_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
order_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
order_date DATETIME,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
In a traditional setup, if you tried to insert an order with a user_id that doesn’t exist in the users table, MySQL would throw an error, preventing the invalid data. Vitess, by default, allows this kind of operation. However, if you want Vitess to enforce these constraints, you need to enable it.
To enable foreign key emulation in Vitess, you’ll typically configure your keyspace or vttablet. The primary mechanism is through the foreign_key_mode setting.
Here’s how you’d enable it for a keyspace using vtctlclient:
vtctlclient --server <vtctl_host>:<vtctl_port> ApplyVSchema -- --keyspace_name <your_keyspace_name> '{
"sharded": true,
"vindexes": {
"user_id_vdx": {
"type": "numeric",
"columns": ["user_id"]
}
},
"tables": {
"users": {
"column_vindexes": [
{
"column": "user_id",
"name": "user_id_vdx"
}
]
},
"orders": {
"column_vindexes": [
{
"column": "user_id",
"name": "user_id_vdx"
}
],
"foreign_keys": {
"fk_user_id": {
"columns": ["user_id"],
"table": "users",
"references": ["user_id"]
}
}
}
}
}'
In this VSchema snippet, we’ve defined a foreign_keys block for the orders table. This tells Vitess that the user_id column in orders references the user_id column in the users table. When foreign_key_mode is enabled (usually via vttablet flags), Vitess will ensure these constraints are checked.
The foreign_key_mode can be set at the vttablet level using command-line flags. For instance, when starting vttablet:
vttablet --tablet-addr <tablet_address> \
--grpc-tablet-addr <grpc_tablet_address> \
--mysql-server-port <mysql_port> \
--keyspace <your_keyspace_name> \
--shard <your_shard_name> \
--service_map="grpc-vtctl,grpc-vtgate,grpc-tablet" \
--foreign_key_mode=ON \
# ... other flags
When foreign_key_mode is ON, Vitess intercepts DML statements that involve foreign keys. For an INSERT into orders, Vitess will first check if the user_id exists in the users table. If it doesn’t, Vitess will return a foreign key violation error before sending the query to MySQL. Similarly, for DELETE operations on users, Vitess will prevent the deletion if there are any orders referencing that user, depending on the ON DELETE and ON UPDATE actions defined in your MySQL FOREIGN KEY constraint.
The key takeaway is that Vitess doesn’t reimplement foreign key logic. Instead, when foreign_key_mode is enabled, it uses the information from the VSchema (which mirrors your MySQL FOREIGN KEY definitions) to perform checks before routing the query to the underlying MySQL instance. This provides a layer of safety and data integrity within the Vitess ecosystem, ensuring that relationships between sharded tables are maintained. It’s crucial that the FOREIGN KEY definitions in your MySQL schema match what you declare in your VSchema for this to work correctly.
A common pitfall is assuming Vitess automatically enforces FKs without explicit configuration. If you’ve defined FKs in MySQL but not in your VSchema and haven’t set foreign_key_mode, Vitess will happily let you violate those constraints.
The next challenge you’ll likely encounter is understanding how Vitess handles ON DELETE CASCADE or ON UPDATE CASCADE actions when dealing with sharded tables, as this can involve coordinated writes across shards.