Foreign Keys ~ 2 Fields to the same Reference field

Hi, I am trying to set up foreign keys on my tables and I ran into a little problem.

I have one table that has an added_by field and then a changed_by field.  Both of these fields are foreign keys to a single field in another table.  

So, I tried this:

  ALTER TABLE employee_org
add CONSTRAINT FK_CHANGED_FIELDS
  FOREIGN KEY (last_changed_by, added_by)
  REFERENCES system_user (user_id);

And it said no way jose!
SQL Error: ORA-00957: duplicate column name
00957. 00000 -  "duplicate column name"

And I also tried adding this:

ALTER TABLE employee_org
add CONSTRAINT FK_CHANGED_BY
  FOREIGN KEY (last_changed_by)
  REFERENCES system_user (user_id);

and then doing the second one:

ALTER TABLE employee_org
add CONSTRAINT FK_ADDED_BY
  FOREIGN KEY (added_by)
  REFERENCES system_user (user_id);

And it said no way jose again!
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause:    an alter table validating constraint failed because the table has
           child records.

So, how do I accomplish my task?
Roxanne25Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
The two foreign key approach is correct.

>>parent keys not found

There is an 'added_by' entry that does not exist in the system_user user_id column.

select added_by from employee_org
minus
select user_id from system_user
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Franck PachotCommented:
Hi,
Your second way is the right one. But it seems that you have already data with a 'added_by' that is not in the system_user  table.
Regards,
Franck.
0
Roxanne25Author Commented:
Ah ok...that makes sense.  Thanks...

Does it make sense to add a foreign key on the last_changed_by field because it will only be populated if someone made a change to the record?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Franck PachotCommented:
Yes, it makes sense. A foreign key can be null. But if it is not null, then its value must be in the referenced table. Of course it makes sense: it checks database integrity, and it tells information to the oracle optimizer.
Regards,
Franck.
0
slightwv (䄆 Netminder) Commented:
>>Does it make sense to add a foreign key on the

It depends on your requirements.  For example:  What do you do if user 'bob' changes a record then quits?

You can't delete him from the system_user table until the foreign key has been resolved.

With no foreign key, the last_changed field will always show 'bob' until the next person changes it.

You might look into a change history table to track changes over time, not just the 'last' change.
0
K_from_OCommented:
The issue is exactly as the database reported: "parent keys not found".  You have records in your child table (employee_org) that are not represented by primary key values in the parent table (system_user).

It is completely acceptable to have a FK that is not populated, if business processes require it, as it would appear they do in this case - some records may never be changed.

What do you plan to do if you receive multiple changes?  You can just keep the most recent, or the first, or you could introduce a trigger to automatically copy any existing change data to a change_history table before overwriting the change info in the employee_org table with the newest change info.
0
slightwv (䄆 Netminder) Commented:
@K_from_O,

I see you are new to Experts-Exchange and I welcome you and appreciate your willingness to help.  I do ask that in the future you read the previous posts before responding.  I really don't see any additional information in your post that hasn't already been covered.
0
K_from_OCommented:
slight:

Yea, some of it was re-stating, I agree.  I did suggest using a trigger to move the history data.  Also, if the answer isn't accepted, does attempting to clarify hurt anything?

0
slightwv (䄆 Netminder) Commented:
>> does attempting to clarify hurt anything?

No but restating what other Experts have already said can be seen as an attempt to poach points from a question.

Since I saw you were new here I just wanted to start you off on the right track.

Agreeing with a previous post and adding additional information is acceptable and actually welcomed.

For example, in this case, something along the lines of:
I agree with everything said.  In case you wanted to track multiple changes I suggest creating a trigger.

or something similar.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.