Dolt is a SQL database with Git-style versioning. With each new version of Dolt, we increase the number of supported SQL features, moving toward our goal of being a complete drop-in replacement for MySQL, while adding all of the versioning features you know and love from Git applied to a database, such as branching, diffs, merging, etc. In our latest release, we added a beta implementation of foreign keys, and in this blog post I'll go over what they are, how to use them, and how they work in a version-controlled database.

What are foreign keys?

Foreign keys are a constraint on a set of columns within a table, with the constrained columns being linked to another set of columns. I'll refer to the column set upon which the constraint is defined on as the child columns (or children), and the linked column set as the parent columns (or parents). With a foreign key constraint in place, the children are restricted to values that are declared on the parent columns. As an example, let's create a foreign key using Dolt.

$ dolt init
Successfully initialized dolt data repository.
$ dolt sql <<SQLBLOCK
> CREATE TABLE parent (
>   pk int PRIMARY KEY,
>   value int
> );
> CREATE TABLE child (
>   pk varchar(10) PRIMARY KEY,
>   parent_value int,
>   FOREIGN KEY (parent_value)
>     REFERENCES parent(value)
>     ON DELETE SET NULL
>     ON UPDATE CASCADE
> );
> INSERT INTO parent VALUES (1, 10), (2, 20), (3, 30);
> SQLBLOCK
Rows inserted: 3 Rows updated: 0 Rows deleted: 0

We've created two tables, one named parent and another named child. child declares a foreign key on its column parent_value, and that column references (or is linked to) the column value on the table parent. We've also inserted a few values into parent, which now looks like:

$ dolt sql -q "SELECT * FROM parent;"
+----+-------+
| pk | value |
+----+-------+
| 1  | 10    |
| 2  | 20    |
| 3  | 30    |
+----+-------+

Because of the foreign key, parent_value may only contain values that are in value. Let's demonstrate this using these tables.

$ dolt sql -q "INSERT INTO child VALUES ('a', 10);"
Query OK, 1 row affected
$ dolt sql -q "INSERT INTO child VALUES ('b', 40);"
foreign key violation on `child`.`fk_child_parent_1`: `(7340,40)`
$ dolt sql -q "INSERT INTO child VALUES ('c', 30);"
Query OK, 1 row affected

We can see that we were able to insert 10 and 30 just fine, however 40 gave us a violation. Let's break down that error message. The first quoted field is the table that declares the foreign key, which is child. The second field is the name of the foreign key. As we did not declare a name when we created the foreign key (by prepending CONSTRAINT your_foreign_key_name), Dolt generated a name for us. The last quoted field provides us two values: the first being the tag of the column (Dolt uses tags to uniquely identify a column, which is viewable via the command dolt schema show or over SQL through SHOW CREATE TABLE), and the second being the value that caused the violation.

You may have noticed ON DELETE and ON UPDATE in the foreign key declaration. These are optional guides to the parent table for when a referenced value is deleted or updated.

Let's see these in action.

$ dolt sql -q "UPDATE parent SET value = value + 5;"
Query OK, 3 rows affected
Rows matched: 3  Changed: 3  Warnings: 0
$ dolt sql -q "SELECT * FROM parent"
+----+-------+
| pk | value |
+----+-------+
| 1  | 15    |
| 2  | 25    |
| 3  | 35    |
+----+-------+
$ dolt sql -q "SELECT * FROM child"
+----+--------------+
| pk | parent_value |
+----+--------------+
| a  | 15           |
| c  | 35           |
+----+--------------+

Here you can see that child now refers to the updated values due to ON UPDATE CASCADE. Let's do a DELETE operation.

$ dolt sql -q "DELETE FROM parent WHERE value = 35"
Query OK, 1 row affected
$ dolt sql -q "SELECT * FROM parent"
+----+-------+
| pk | value |
+----+-------+
| 1  | 15    |
| 2  | 25    |
+----+-------+
$ dolt sql -q "SELECT * FROM child"
+----+--------------+
| pk | parent_value |
+----+--------------+
| a  | 15           |
| c  | <NULL>       |
+----+--------------+

The value was removed from parent as expected, and the matching value in child was set to NULL. In fact, NULL is regarded as a special value with foreign keys. Unlike other values, a child can insert NULL without it existing in the parent, and UPDATE/DELETE operations on a NULL in the parent do not consider the ON UPDATE/ON DELETE options.

Foreign keys and versioning

As mentioned earlier, Dolt is not only a SQL database, but a SQL database built on top of Git's style of versioning. All of the familiar commands that you know and love from Git are available in Dolt, such as commit, branch, merge, diff, checkout, and more. They also function just as you'd expect, except that Dolt applies these commands to schemas and table data, while Git applies them to code. Tim wrote a blog going over how some of these map to our database, which I highly recommend reading. With foreign keys, the story is no different: they follow your commits and branches the same as your schema does.

With the addition of foreign keys, we've decided to rewrite how we handle merge conflicts, especially in regard to non-data conflicts. There are several ways to get foreign key conflicts, but one example would be for two branches to add a foreign key with the same name, yet use different columns. We considered displaying that there was a conflict, forcing the user to get one of the branches into a similar state as the other before merging, but we've decided that a user interface that allows you to do these mid-merge allows for a better user experience. These conflict changes are currently in testing within the company, so expect them to land in a new release soon!

Conclusion

Foreign keys are a powerful way of enforcing structure within a database. Dolt now has this capability, bringing us one step closer to having full SQL compatibility. Getting started with Dolt is extremely simple, and you can try all of the above just seconds after downloading Dolt. If you encounter any bugs or unexpected behavior, file an issue and we'll reply as soon as we can. If you want to contribute, then send a pull request our way! We hope you'll stay along for the journey as we make Dolt the best program that it can be!