Declarative Referential Integrity and Procedural Referential Integrity

In order to make the database (or table) consistence, one of the integrity types we use is Referential Integrity. It is implemented using a Foreign Key constraint and it makes sure that update or delete of a referenced value is prevented when it is exist in the foreign key column. This makes sure that we do not see inconsistency with values or records such as An Order without a customer, Customer address with an unknown country because Foreign Key constraint does not allow to insert an Order without setting a valid value to the foreign key column referencing customer table, or it does not allow to set a wrong country in the customer table when it is set as a foreign key to the country table. However, there are some instances that it requires some additional validation based business logic which lead us to implement this in a different way.

Declarative Referential Integrity

This is what we discussed above. The validation required is implemented using Foreign Key constraint to maintain the integrity. This makes sure that referenced value cannot be updated or deleted if it is referenced by a foreign key and it does not allow to have a value in referencing column with a value that is not exist in the referenced column..

Procedural Referential Integrity

This does the same but we handle it as we need, rather passing the responsibility to the DBMS. This is implemented with Triggers. Example, assume that the Customer table has a column called CountryId which is a foreign key, referencing Country table, and when a customer is inserted, if the customer type is web, we need to set the CountryId to -1. Then the logical has to be handled by us and we can implement it with a AFTER TRIGGER. Another example is, assume that CountryId should be set only when the customer has placed certain number of orders for a specific country during insert and update, then we can use INSTEAD OF TRIGGER for checking before the operation as this needs to check with some other tables as well, and if it does not reach the threshold, we can rollback the operation. This is Procedural Referential Integrity.