Foreign Key Constraint with HR_Staff  Topic is solved

Find general Jiwa support here.

Foreign Key Constraint with HR_Staff

Postby JamesNewman » Mon Dec 08, 2014 3:03 pm

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.
JamesNewman
I'm new here
I'm new here
 
Posts: 3
Joined: Mon Dec 08, 2014 2:59 pm

Re: Foreign Key Constraint with HR_Staff  Topic is solved

Postby Scott.Pearce » Mon Dec 08, 2014 3:06 pm

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?
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Foreign Key Constraint with HR_Staff

Postby Mike.Sheen » Mon Dec 08, 2014 3:43 pm

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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Foreign Key Constraint with HR_Staff

Postby Scott.Pearce » Mon Dec 08, 2014 4:03 pm

+1 for trigger.
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Foreign Key Constraint with HR_Staff

Postby JamesNewman » Mon Dec 08, 2014 4:33 pm

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.
JamesNewman
I'm new here
I'm new here
 
Posts: 3
Joined: Mon Dec 08, 2014 2:59 pm

Re: Foreign Key Constraint with HR_Staff

Postby Scott.Pearce » Mon Dec 08, 2014 4:39 pm

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.
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221


Return to Core Product Support

Who is online

Users browsing this forum: No registered users and 3 guests

cron