Currently, creating a foreign key between two tables requires that both tables have a "relevant" index that can be used to look values up precisely on both sides.
For example, consider a foreign key from a customers table to a zipcodes table, where a zipcode column in customers references the id table in the zipcodes table. Currently, there needs to be two indexes:
- on the
id column of the zipcodes table, to permit fast checks for zipcode existence when adding a new customer
- on the
zipcode column of the customers table, to permit fast checks for any customers that reference a particular zipcode when deleting a row from the zipcodes table.
The first requirement is reasonable, since most schemas with foreign keys commonly need to support inserts into the referencing table.
The second requirement is somewhat unreasonable, because it's very common for a schema to have a reference table that never is deleted from at all. In this case, like the zipcodes table in our example, requiring that "reverse" index on the customers table is a waste of storage and compute resources, since every insert into customers now also requires updating that other index.
This issue tracks permitting foreign key relationships that don't require an index on the source side. I expect that this work will be mostly driven by the movement of foreign key checks into the optimizer. Once the optimizer is the entity deciding which index to use for reverse lookups on deletes, then this issue can be closed, since the optimizer should be able to decide that a precise lookup is unavailable and therefore require falling back to a full table scan, which is the desirable outcome in this scenario.
Currently, creating a foreign key between two tables requires that both tables have a "relevant" index that can be used to look values up precisely on both sides.
For example, consider a foreign key from a
customerstable to azipcodestable, where azipcodecolumn incustomersreferences theidtable in thezipcodestable. Currently, there needs to be two indexes:idcolumn of thezipcodestable, to permit fast checks for zipcode existence when adding a new customerzipcodecolumn of thecustomerstable, to permit fast checks for any customers that reference a particular zipcode when deleting a row from thezipcodestable.The first requirement is reasonable, since most schemas with foreign keys commonly need to support inserts into the referencing table.
The second requirement is somewhat unreasonable, because it's very common for a schema to have a reference table that never is deleted from at all. In this case, like the
zipcodestable in our example, requiring that "reverse" index on thecustomerstable is a waste of storage and compute resources, since every insert intocustomersnow also requires updating that other index.This issue tracks permitting foreign key relationships that don't require an index on the source side. I expect that this work will be mostly driven by the movement of foreign key checks into the optimizer. Once the optimizer is the entity deciding which index to use for reverse lookups on deletes, then this issue can be closed, since the optimizer should be able to decide that a precise lookup is unavailable and therefore require falling back to a full table scan, which is the desirable outcome in this scenario.