![]() Interestingly, it's okay for VARCHAR(32) vs VARCHAR(64) in child and parent. An INT column on the child may not reference a BIGINT column on the parent. The child's columns must match the referenced parent columns in count and in data type.Optionally, the index may proceed to cover more columns. There has to be an index covering the referenced columns in the same order they're referenced by the constraint. The referenced columns in the parent must be indexed in-order.The foreign key reference columns in the parent table must exist.The referenced (parent) table must exist.As a special case, a table may reference itself as its parent. A foreign key constraint is a relationship between a parent table and a child table.Consider that in MySQL (or in InnoDB, rather, as we will discuss later on), the following rules must apply for any foreign key constraint definition: However, the semantic analysis is much more involved. Supporting the foreign key constraint schema definitions is relatively simple, and is but a matter of understanding the SQL syntax involved. PlanetScale uses three-way merge to determine the diffs. ![]() The Deploy request page shows the user the semantic diff between the main ( base) branch and their own ( head) branch. Once they're ready to deploy those changes, they submit a Deploy request, which lets them review the schema changes they've made before deploying them to production. With PlanetScale branching, the user works in a development environment where they're free to make schema changes. The first challenge we had to deal with was handling branching and deploy requests. As you will see in this post, these are all closely intertwined. It turns out, we had to overcome challenges in almost every single layer of the product: some due to MySQL limitations, some due to how we want to incorporate foreign key support with PlanetScale's (through Vitess) Online DDL, and some due to branching and schema analysis logic. Around a year ago, we decided to really dive in to see what it would actually take to support them. It was a realization that came while we were building the product, and we took it as a fact of life, something we'd have to work around. When we launched PlanetScale, we refrained from supporting foreign keys constraints, first and foremost because we could not make them work with branching and Online DDL. To do so while still delivering Online DDL, gated deployments, online imports, and eventually cross shard support, is another. To support foreign key constraints is one thing. So, what was the problem? Why did it take so long? It's something that any and every database supports, right? Yes and no. This has been in the making for quite a while - around a year in fact. Today, PlanetScale announced support for foreign key constraints.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |