While working on a Legacy Application with Legacy Database design as part of fixing a bug, I thought this bug would not have ever happened if a particular column was defined as Non Nullable since this particular column was the identifier to the parent table. We had a Customer table and the all the names a customer could have like LegalName, LongName, ShortName etc are stored in the CustomerName table. CustomerName cannot exist without Customer hence its logical that the CustomerName.CustomerID column cannot be nullable and should also have a Foreign Key constraint enforcing the relationship. Implementing just the Foreign Key constraint is not enough since the application could potentially be inserting null values in the CustomerName.CustomerID creating orphan records.
The changes we had to make to the legacy application and legacy database involved
1. Find and fix the place where the application creates or updates CustomerName so that it does not create a CustomerName with Null or Invalid CustomerID values. I did it by using the Applications Customer and CustomerName domain objects and the business logic that created these objects and wrote a unit test that inteacts with the database. So that the object was persisted and I could see the resulting rows in the database. So once I do Step 2 and 3 below, this unit test will fail. The failing test will point me to the problem in the application code.
2.Write a SQL Script to fix the data in CustomerName, such that we delete all CustomerName rows where the CustomerName.CustomerID is null and also where CustomerName.CustomerID does not exist in Customer.CustomerID. The reason I did this was all the CustomerName rows where CustomerID was null where orphan rows that could not be reached too by the application anyway and hence could be removed.
3.Create a Foreignkey Constraint between Customer and CustomerName on the CustomerID column.
4.Now that we have no nulls in the CustomerName.CustomerID column and all the refrentially improper data has been removed. We run our unit test from Step 1. THe failure will point us to the places in the application code where we need to fix the code.
5.Now our applcation code has been changed and we improved it, we have improved the the database design, in the process we also have a unit test that will break when anyone else makes any application code changes that break the Refrentially Integrity and/or the Not Null rule.
Overall a satisfying day at work I think.