referential integrity

[CJ_London]

There is no doubt that enforcing referential integrity (a child record cannot exist without a parent record) is a good thing to do, But the blanket statement that a design is not good because you cannot enforce RI is not always true.

You can have a child record providing the parentFK is null. It is only when you assign a value to the parentFK, that value must exist as the primary key in the parent table and referential integrity can be enforced.

This feature can have benefits. For example in a cashbook type accounting system you have income/expenses and bank transactions. The expense occurs first and the bank transaction later, but the transaction is the parent since one transaction (a payment) might cover many expenses. So you leave the expense parentFK as null and when the payment occurs in the bank statement assign the transactionPK to the parentFK(s).

Once you have done this assignment, referential integrity kicks in and you cannot delete the bank transaction - but you can still edit the expense parentFK or even delete the record however you can use code and/or conditional formatting to prevent this happening

You could say don't enter the expense until the bank transaction occurs but a) this is not always convenient and b) by summing the income/expenses with a null parentFK you have a rudimentary view of income and expense accruals - accruals are not normally part of a cashbook system.

If you truly want to fully enforce referential integrity so no child record can exist even with a null parentFK then you also need to set the parentFK required field to yes.

 

[Pat Hartman]

Don't confuse optional relationships with required relationships. BOTH support RI.

Another example of an optional relationship is a company car pool. There is a vehicle table and an employee table. A vehicle can only be assigned to one employee at a time but it may also be unassigned to any employee. Therefore, you enforce RI on the vehicle-Employee relationship but the default for the EmployeeID is null and it is not required. In a hierarchical relationship, the parent record is ALWAYS required. This is something like Order-OrderDetails. In this case, the default for OrderID is still null BUT, the field is required. Therefore, you cannot have an OrderDetails record without a related Order record.

There are other "lookup" type relationships which again, may be defined as required or optional. For example, your application may define a default value for marital status such as "unknown" in which case, you could make it required. But some applications allow this type of personal information to be unspecified, in which case it would not have a default and the field would not be required. But RI would still be enforced because if there is a non-null value, it MUST exist in the lookup table.

 

[CJ_London]

Don't confuse optional relationships with required relationships. BOTH support RI.

I wasn't

 

[Pat Hartman]

The Point is that in your description, the relationship is optional. Defining RI does NOT interfere with that. The unassigned record can still exist and it isn't until a non-null value is placed in the FK does RI get activated. Once you connect the child to a parent, the parent MUST exist AND unless you have specified cascade delete (which you would not do for an optional relationship, you will NOT be able to delete the "parent" as long as a "child" is related to it.

I guess I don't understand what you are saying. Are you recommending to NOT use RI when the relationship is optional?

 

[CJ_London]

No, just pointing out that many newbies think that RI means a record cannot exist in a child table without a parent.

when in fact a record can legitimately exist if the fk is null. And if the developer does not want that to be a possibility then they need to set the fk field to required

you used the word ‘optional’, I didn’t. Otherwise you are just restating my comments

 

[Pat Hartman]

It sounds to me like you are recommending that RI not be enforced when the relationship is optional and I was trying to get you to clarify that.

But the blanket statement that a design is not good because you cannot enforce RI is not always true.

You CAN and SHOULD still enforce RI even for an optional relationship. Enforcing RI does not prevent you from having an unassigned "child" record. It still prevents you from trying to use an invalid value for the FK and it still keeps you from deleting the "parent" once it has a "child" UNLESS you specify cascade delete BUT, it would NEVER be logical to specify Cascade delete for an optional relationship. In the case of the motor pool example, Cascade delete would end up deleting an employee record if you deleted the vehicle. That makes no sense and that logic is quite likely to prevail for other optional relationships you encounter. State, is another potential one. If the customer picks up an order, a shipping address is optional so the StateCD is optional and therefore the relationship between Order and State is optional. If you deleted California, you wouldn't want to delete all the orders that had ever shipped to California.

 

[CJ_London]

Thanks for providing additional clarification for others

 

[Pat Hartman]

@CJ_London I can't tell whether you are deliberately ignoring my question or not but OK whatever. Sounds like you agree. Even with an optional relationship, you are not advising against RI.

 

[CJ_London]

Are you recommending to NOT use RI when the relationship is optional?

This the only question I can see , and I answered no

 

[Pat Hartman]

Thanks

 

[Galaxiom]

I would have expected transaction and expense to be a many to many. An expense can be paid over multiple transactions. A transaction can pay multiple expenses,

 

[GPGeorge]

I would have expected transaction and expense to be a many to many. An expense can be paid over multiple transactions. A transaction can pay multiple expenses,

That seems to me to be the basis of a Business Rule. Business Rules tend to be specific to businesses that invent their own rules.

I suspect you are right for the majority of mature businesses, though.

 

[Pat Hartman]

Sometimes I argue with clients about business rules that are bizarre but, in the end, if it is not illegal, immoral, or fattening, I warn them and just do it.

 

[Galaxiom]

Sometimes I argue with clients about business rules that are bizarre but, in the end, if it is not illegal, immoral, or fattening, I warn them and just do it.

I have had cases where I could see the reality would be that the program settings would need change in the future, despite users assuring me that it wouldn't. So built in what made sense and simply hid the extra functionality from the front end.

Sure enough, before long, the request to change the configuration was requested and I was able to make the changes in record time.

 

[CJ_London]

An expense can be paid over multiple transactions. A transaction can pay multiple expenses,

don't disagree for a double entry accounting system, but the example I used was for a cashbook system.