I’ve been designing relational databases since the mid 80’s. My conclusion is that the relational model is weak and lacking. Specifically, the foreign key – that simple DRI constraint which is the keystone of the relational model - is insufficient. The model poorly represents reality.
First, The foreign key can ensure that a FK references an existing PK, but is unable to ensure the PK is valid as the recipient of the FK because the FK can’t define valid workflow state of the PK. For example, a paycheck FK can reference the PK of a terminated employee, or an order detail FK can sell a product PK even if the product is unavailable for sale. The FK only checks for the presence of the PK in the primary table.
A stronger model would include the workflow state of the PK in the definition of the FK.
As a workaround, developers have three options. Code the check in a trigger (extending transaction duration), code the check in a sproc (best option) or logical data access layer, copy valid PKs into a “bucket table” and point the FK to the bucket table (cheesy). (The Nordic object/relational database for SQL Server includes optional workflow state in its association definition.)
The second limitation of the relational model is that the basic DRI constraint is a one-way check. Let me explain, in reality there are two types of PK-FK relationships – a has-a relationship, and a contains relationship.
In a has-a relationship, one item is loosely connected to another item. The presence or lack of presence of the connected item doesn’t change the existence of the primary item. Either way the primary item is a valid item. For instance, an employee is a valid employee even if it doesn’t have any connected paychecks. The relational model handles this example well.
The second type of relationship is a contains relation. In this case, the primary item is constructed of the secondary item, and without the presence of the secondary item, the primary item itself is invalid. A very common example is an order-order detail relationship. We’ve used this as the classic primary table/secondary table example for a few decades, but think about it again. An order without any order detail rows is an invalid order. The relational model teases this need with cascading deletes, but that only addresses some of the problem at the trail end. To truly address contains relationships there needs to be a check to ensure that the PK has at least one valid FK by the end of the logical transaction.
The relational model was a breakthrough in the mid 60’s, but unless the model can continue to develop, it will eventually be overrun by OOA/D.