I have database that handles maintenance records for buildings in various office parks around the country. The country is divide up into regions and one of the the office in one of these office parks is responsible for handling the records for all the office parks in a given region. There is a stored procedure that is used to "move" responsibility for work order records associated with a particular building from one region to the managing office located in another region. The problem occur when one work order applies to two buildings (do a preventive maintenance on the rooftop air conditioners in building a and building b), and management decides that responsibility for one of these buildings is to be shifted to another regional HQ. In this case, a new work order is created with the HQ Name field being changed to the receiving HQ for the one building involved. The original work order remains associated with the second building that isn't being "moved". Each work order has a Work Order Number which is unique within a region but could exist in the receiving region. In this case, a new work order number is assigned and a record is created in a history table so that if someone is trying to track work orders they can find out what happened to this work order. The problem arises when the second building is moved. It is assigned to the work order in the receiving HQ that was created when the firs building was "moved". The work order that was associated with the original regional HQ is then deleted and the history record remains.
Somewhere along the line, after this stored procedure was tested and working, there was a foreign key constraint added to the history table that prevents deleting the old work order record. The Change Reason field for the insert statement in the history was set up to be a particularly long phrase so that they could be found and there would not be any coincidental erroneous matches. Is there any way to get around this constraint and be able to delete any work order linked to the history table when the history contains this particular phrase? (There is no provision in the work order table to create a "soft" delete.)
Somewhere along the line, after this stored procedure was tested and working, there was a foreign key constraint added to the history table that prevents deleting the old work order record. The Change Reason field for the insert statement in the history was set up to be a particularly long phrase so that they could be found and there would not be any coincidental erroneous matches. Is there any way to get around this constraint and be able to delete any work order linked to the history table when the history contains this particular phrase? (There is no provision in the work order table to create a "soft" delete.)