Jiwa

We have a problem where the StaffID has been set to nullable on the GL_Transactions table, if we revert this back to its previous state on a temporary basis, will it cause other problems. We need to do this to keep another import interface working that cannot be changed before upgrading to the next version.

I think you mean that your problem is the fact that a foreign key now exists on the GL_Transactions.StaffID column. Further, your import is placing a non-valid StaffID into that column, causing a foreign key violation. Yes?

JamesNewman wrote:We have a problem where the StaffID has been set to nullable on the GL_Transactions table, if we revert this back to its previous state on a temporary basis, will it cause other problems. We need to do this to keep another import interface working that cannot be changed before upgrading to the next version.

Hi James,

If you make the StaffID Column NOT NULLable on GL_Transactions then this may cause issues - we won't know unless we do all our regression testing with that schema change. I would have to assume it would cause issues. You can probably get away with the temporary removal of the foreign key constraint - if that is what is actually causing your issue (As Scott suggested) - but you might find reports or screen information becomes inconsistent as it may expect the StaffID on that table to be valid and thus joins to the HR_Staff table will cause records to be omitted.

The recommended action is to only insert into that table either with NULLs or a valid value. A trigger should be able to do that for your purposes.

+1 for trigger.

We will have to continue using it with the foreign key restraint removed from now, but we will then use a script after the import to set the value to null, this will then keep the database consistent until the interface is fixed. Does this sound okay.

I guess. The fact that it is null-able implies that LEFT JOIN's would be used in our code and reports. Which means you should be safe.