?
Solved

Foreign Keys ~ 2 Fields to the same Reference field

Posted on 2010-03-25
9
Medium Priority
?
792 Views
Last Modified: 2013-11-11
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?
0
Comment
Question by:Roxanne25
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 800 total points
ID: 28560484
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
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 1200 total points
ID: 28560674
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
 

Author Comment

by:Roxanne25
ID: 28565182
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 1200 total points
ID: 28566605
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 28567909
>>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
 

Expert Comment

by:K_from_O
ID: 28569015
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 28571652
@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
 

Expert Comment

by:K_from_O
ID: 28572535
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 28573150
>> 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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question