Hi Nancy,

I've been thinking about this a little and trying to completely understand it. I don't think we can call RI a business rule. RI is a noun, referential meaning "Having references" and integrity meaning "Complete". Referential Entegrity means "All references are complete". So a database either has RI or it doesn't.

In our world that translates to all references in a database are complete. All fields that contain foriegn key values must have matching records in the parent tables.

Say we have the following 2 tables:
CUSTOMER
--------
pk_cust

INVOICE
--------
pk_inv
fk_cust

Customer table has 1 record with pk_cust = 23
Invoice table has 1 record with pk_inv = 50 and fk_cust = 23

These tables have RI. All fk's have matching records.

If we delete customer record 23 the database no longer has RI. Because the Invoice table has a foriegn key value that does not match a record in the customer table.

A business rule might state that 'When customers are deleted leave the invoices'. Using the above table structures, this rule will cause the database to not have RI.

However, we can enforce the business rule and maintain RI by using a linker table.

CUST_INV_LINK
--------
fk_cust
fk_inv

and remove the foriegn key from the invoice table.

INVOICE
--------
pk_inv

By removing the foriegn key from the invoice table we can delete the customer record and the records with a matching fk_cust from CUST_INV_LINK and maintain RI in the database.

The Invoice records will still remain and have no unmatched foriegn key values (since the fk's have been moved out of that table). So the tables now have RI once again and the business rule has been inforced.

RI can only exist in the data tier, as it describes the state of a database. Evan had posted a quote form the wiki "Referential Integrity (RI) is dictated by business rules, not database design rules". IMHO that is incorrect. A database can always have RI regardless of what the business rules state.

IAC these are my thoughts on the subject, if I am completely wrong someone please correct me. Thanks.


>I originally thought it belonged in the data tier, but I've recently been convinced otherwise.
>
>RI is a business rule because without it in the database, the database is still valid (i.e. it's not corrupted), but whether it makes sense to leave invoices behind after deleting their customer is a business rule. And that is what RI is.
>
>>Any other comments on this? I have always thought of RI as being a data tier issue but here is a quote from the Foxwiki