Dolt is the world's first version-controlled relational database. Dolt lets you branch, fork, clone, merge, and diff your relational data, in all the same ways Git lets you work with your files. In today's blog, we're taking a look at a new feature we built for a customer to allow them to declare foreign keys that reference the dolt_branches system table.

Customer Request

Recently, one of our customers was telling us about how they use Dolt branches. For each branch, they track extra metadata, such as who within their organization "owns" that branch. They maintain this info in a table, named extended_branch_info and there is a column called branch_name that matches up to the name column in the dolt_branches system table. Their application automatically creates this tracking information as new branches are created. This is a natural candidate for a foreign key relationship between the extended_branch_info(branch_name) column and the dolt_branches(name) column. Unfortunately, when they tried to create a foreign key constraint between their table and the dolt_branches system table, they got the error message below, because Dolt didn't support creating foreign keys that reference system tables.

the table does not support foreign key operations: dolt_branches

When they told us about this, we thought this would be a pretty cool use case to support. Tracking extra metadata for Dolt branches seems like a common need many customers would have, and it feels intuitive that you should be able to declare a foreign key referencing the dolt_branches system table. There are several benefits of modeling this foreign key relationship here:

1. Explicitness – by modeling a foreign key relationship, we add metadata that explicitly defines the relationship between two tables. Users and tooling can look at the database schema and see this explicit relationship, so just in case it wasn't already obvious that the branch_name field in our extended_branch_info table referenced a Dolt branch, the explicit foreign key relationship makes it abundantly clear. This is particularly helpful for tooling such as workbench UIs or even AI agents, since they can introspect a schema and use explicit metadata like this to quickly understand relationships in the data.
2. Data Consistency – by requiring that the referenced table has a matching branch name, the database guarantees data consistency. The only way a new row can be inserted into the extended_branch_info table is if it matches to a valid, existing branch from the dolt_branches system table. This removes the chance that buggy code may insert an incorrect branch name.
3. Referential Actions – foreign key referential actions allow you to restrict or cascade data changes from the referenced table to the tables that declare foreign keys on those referenced tables. This isn't actually supported in our initial release of this feature, as we'll explain further down in this post. Despite that, foreign key referential actions are another powerful data consistency feature. Dolt already supports referential actions for foreign keys between user tables, but as we'll explain below, system tables have some unique properties that make this more challenging to support.

As of Dolt 1.59.7 we now support creating foreign keys that reference the dolt_branches(name). In the next sections, we demonstrate how to use this feature, talk about the implementation, and then explain some caveats of this initial implementation. We currently only support this for the name field and the dolt_branches system table, but extending it to other fields in other system tables is straightforward. Before we add support for other system tables we wanted to get some customer feedback on this first step and make sure this first piece worked well for customers.

Demo

If you don't already have dolt installed on your system yet, we provide several ways to install it, depending on your system. Once you have dolt installed, create a directory and initialize it for a new Dolt database:

mkdir db1;
cd db1;
dolt init

Now that you have a Dolt database, you can open a SQL shell by running:

dolt sql

From inside the SQL shell, let's first take a look at the dolt_branches system table:

SELECT * FROM dolt_branches;

Since we just initialized this new database, there is only one branch that exists so far:

+------+----------------------------------+------------------+-------------------------+---------------------+----------------------------+--------+--------+-------+
| name | hash                             | latest_committer | latest_committer_email  | latest_commit_date  | latest_commit_message      | remote | branch | dirty |
+------+----------------------------------+------------------+-------------------------+---------------------+----------------------------+--------+--------+-------+
| main | bd4ieaeb2srjgcgke9n2jqdanr209hs1 | jason            | jason@dolthub.com       | 2025-09-12 21:48:01 | Initialize data repository |        |        | false |
+------+----------------------------------+------------------+-------------------------+---------------------+----------------------------+--------+--------+-------+
1 row in set (0.00 sec)

Let's create our extended_branch_info table, including an inline declaration for the foreign key constraint that references dolt_branches:

CREATE TABLE extended_branch_info(branch_name varchar(300) primary key, owner varchar(255), included_in_backups bool, development_only bool, 
            CONSTRAINT fk_branch_metadata_branch FOREIGN KEY (branch_name) REFERENCES dolt_branches(name));

We now have a new table created and any rows we insert MUST reference a valid, existing Dolt branch. Let's try inserting some data for the main branch:

INSERT INTO extended_branch_info VALUES ('main', 'Jason', false, true);

Sure enough, since main is the name of an existing branch from the dolt_branches table, our row was inserted successfully:

SELECT * FROM extended_branch_info;
+-------------+-------+---------------------+------------------+
| branch_name | owner | included_in_backups | development_only |
+-------------+-------+---------------------+------------------+
| main        | Jason | 0                   | 1                |
+-------------+-------+---------------------+------------------+
1 row in set (0.00 sec)

Just for fun, let's see what happens when we try to insert a row that references a branch name that does NOT exist:

INSERT INTO extended_branch_info VALUES ('not a real branch', 'Jason', false, true);

Just like we'd expect, Dolt doesn't let us insert this row, since it would invalidate the foreign key constraint:

cannot add or update a child row - Foreign key violation on fk: `fk_branch_metadata_branch`, table: `extended_branch_info`, referenced table: `dolt_branches`, key: `[not a real branch]`

Implementation

Implementing this feature broke down into two chunks of work: 1) create a virtual index by branch name on the dolt_branches system table (because foreign keys require an indexed path to efficiently look up branch name values), and 2) support system tables being used in foreign key relationships.

We've talked about how we simulate system table indexes in the past, and for data like commits, that is stored in prolly tree data structures, we can do some clever tricks to efficiently jump to specific commits in the stored data. However, branch names aren't stored in a data structure that allows us to quickly jump into the data, so our index implementation here isn't particularly clever and searches through the full set of branch names to find the data. However, because the average number of Dolt branches tends to be small, especially compared to other data, like commits, there's much less of a need to optimize here, and a naive implementation will work just fine. In practice this means we don't speed up queries to the dolt_branches table, but we also don't slow them down. The real advantage here is that we get the sql.IndexedTable interface we need in order for the foreign key logic to interact with this system table.

Once we implemented the sql.IndexedTable interface for the dolt_branches system table and created support for a virtual index on the name column, next we needed to implement the sql.ForeignKeyTable interface. Although the sql.ForeignKeyTable interface has many more methods than the sql.IndexedTable interface, it turned out to be the easier of the two interfaces to implement, since the majority of those methods could simply return an error since we only needed to support a subset of foreign key operations. Because we only wanted the dolt_branches system table to be referenced by a foreign key, and didn't need to add support for creating or changing foreign keys on the dolt_branches table itself, there wasn't much logic that needed to be wired together here.

Caveats

There are two caveats with this feature. The first is that we currently only support referencing dolt_branches(name) for foreign keys. This was done simply to build exactly what our customer needed, then roll it out for feedback before we expand on it. Once we've validated the feature with customer feedback, we're more than happy to extend this feature to other columns in additional system tables. Just send us a GitHub issue to let us know if there's another column or system table you'd like to reference with a foreign key.

The second caveat is that foreign key referential actions are not supported yet. Referential actions allow you to automatically update data in a table that declares a foreign key when the referenced data changes. For example, if table child has a foreign key that references the parent table, the referential action on that foreign key allows you to automatically update the data in child if the data in parent changes, or to delete the data, or to restrict the change from happening entirely. Trying to set a referential action on a FK that references a system table leads to an error like this:

ALTER TABLE t ADD CONSTRAINT fk123 FOREIGN KEY (branch_name) REFERENCES dolt_branches(name) ON DELETE CASCADE;
foreign keys referencing Dolt system tables do not support referential actions

We decided to leave this out of our initial support for foreign keys to system tables because system tables often hold global data and are accessible from all branches. In other words, the dolt_branches table holds the same data, no mater what branch you have checked out, and creating or deleting a branch will update the data in dolt_branches that can be seen from every branch. So, what's the desired behavior if a branch is deleted? Should Dolt update tables that referenced that branch for every branch in the database? Or should it just update the currently checked out branch? Customers may have historical branches where they don't want changes made. If the customer does want every branch updated, does the change just get left in the working set? That may confuse or surprise the next person who comes along and tries to commit a change on that branch. Should Dolt automatically create a new commit for that change? That could also risk surprising customers.

In addition to the challenges with defining the "correct" behavior, there are also issues with atomically making these changes across multiple branches. Dolt's storage APIs don't support making atomic updates across multiple branches, so there's a race condition here. Consider the case where multiple clients are connected to different branches on a Dolt server. If a branch is removed from the dolt_branches table, then multiple branches may need to be updated due to referential actions. These updates can't currently be done atomically, so there's a chance that clients may see inconsistent data – they may query the dolt_branches and extended_branch_info tables and see that data in the extended_branch_info doesn't match up to an existing branch anymore. The effects of this data inconsistency are specific to each application and how it's using the data (as well as how defensively it checks for data consistency issues), but in many applications this could cause issues.

Conclusion

We love building new features based on direct customer feedback. Adding support for foreign keys that can reference system tables came directly from a customer and was a fun feature to build. This feature is initially available for foreign keys that reference dolt_branches(name), and we're happy to expand support to more system tables if you need it. Just drop us an issue on GitHub, or swing by our Discord and say hello. We'd love to hear from you and help you use Dolt!