Foreign keys
************

What is a foreign key?
======================
The technical definition of a `foreign key <https://en.wikipedia.org/wiki/Foreign_key>`_, also known as FK, is a set of attributes on a table that refer to the primary key of another table. Often this will be represented as a column referring to another table's ID field. This relationship isn't just a reference since it is enforced by the database. For example, a row that is the parent of several FKs will fail deletion before all of the relationships have been severed (i.e. each of the rows with FKs are deleted). In the case of bframe, no core models use FKs.

Why not use foreign keys?
=========================
The version and ID would need to be present on the referenced table for a FK to be valid. This is possible, but for every new version of the primary key referenced every row with a FK would require the updated version. Since bframe is append-only, this means creating an entirely new tree of objects when ever a parent object has the version bumped. Avoiding this amount of overhead is desirable and led bframe to a design without FKs. References within bframe are made solely to the ``id`` or ``durable_key`` fields. The ``version``  field is only used for filtering purposes in a view referenced by the bframe client. This is a high level representation of what it could look like:

.. code-block:: sql

    CREATE VIEW customers AS
        SELECT raw.* EXCLUDE(rank)
        FROM (
            SELECT raw.*, ROW_NUMBER() OVER(
                PARTITION BY raw.id
                ORDER BY raw.version DESC
            ) AS rank
            FROM src.customers AS raw
        )
        WHERE rank = 1;

There are pros and cons to this approach. The benefit is simple, an updated version does not need to update or insert new copies for all pseudo-FKs of the former row. This makes it simple to create new versions with little to no overhead. On the otherside the largest issue is reconciling past results with new versions. While this isn't perfect, this is a known problem and processes for reconciliation are still possible. 

.. TODO(need to link to some thoughts on how to handle reconciliation)